[急+500论坛币] 请详细解释下列SAS语句的作用

data temp5;
set temp;
if code='0055' then
  do;
  r1=0.5*r;
  p=&a;         
  end;
if code='0001' then
  do;
  r1=0.3*r;
  p=&a;
  end;
if code='0009' then
  do;
  r1=365*0.05;
  p=&a;
  end;
run;

/*新的一只*/
proc sql;
  connect to oracle as conname  (&risk);  
  create table temp as select * from connection to conname   
(
select bizdate,a.code,a.name,close_price,bizdate2,close_price2,close_price/close_price2-1 as r,
       to_date(bizdate,'yyyy-mm-dd')-to_date(bizdate2,'yyyy-mm-dd') as interval      from
(select bizdate,code,name,close_price

把代码分为三部分，其中为R000001, R000002写的部分都没有insert到数据库，所以，应该不重要，先说最后一部分。
我的vi突然不能输入中文了，所以就容忍一下垃圾英文。说明在最开头。建议拷贝到SAS editor或者其他editor看。
=============================最后部分

1. /*
   
2. * zx_wind_bench.bizdate=TB_OBJECT_1101.f13_1101 in ( 20140311:&date_str)
   
3. * baseline = min(20140311, &date_str)
   
4. * zx_wind_bench.seccode=TB_OBJECT_1101.f16_1090='000535'
   
5. * p='R000003'
   
6. * 1. compute the daily average add increase rate for each bizdate at the base level.
   
7. *    a. compute the daily average 'add' for seccode '00053' between '20140301' and &date_str from table TB_OBJECT_1101.
   
8. *    b. find the baseline daily average add.
   
9. *    c. compute the daily average add increase rate for each bizdate at the base level. {rate = daily_avg_add/avg_add_base -1}
   
10. * 2. compute bizdate add_nav increase rate at base level. -> add_r
    
11. * 3. compute bench increase rate at base level.
    
12. *    a. select bench from table zx_wind_bench for seccode '000535' where its date between '20140311' and &date_str
    
13. *    b. find the baseline bench to the bench which is corresponding to the first bizdate between the date interval.
    
14. *    c. compute the increase rate for each bizdate as the base level.  {rate = daily_bench/bench_base -1 }
    
15. * 4. insert those two value into (increase bench/add rate) into table fkdb.pf_all_bench  
    
16. *    a. delete corresponding records from table  fkdb.pf_all_bench  
    
17. *    b. insert
    
18. *
    
19. * Entity Definition and Mapping:  FKDB.PF_ALL_BENCH
    
20. *        'R000003'                                                                      as p
    
21. *      , zx_wind_bench.sec_name                                                         as panamecn  :chinese name corresponding to seccode 000535
    
22. *      , zx_wind_bench.bizdate=TB_OBJECT_1101.f13_1101 in ( 20140311:&date_str)         as bizdate
    
23. *      , avg(TB_OBJECT_1101.F21_1101)                                       across date as nav
    
24. *      , avg(TB_OBJECT_1101.F21_1101)                                       across date as add1
    
25. *      , avg(TB_OBJECT_1101.F21_1101).[{bizdate=*}/{bizdate=20140311}] - 1  across date as add_r
    
26. *      , zx_wind_bench.bench.(&date_str/20140311)-1                                     as r
    
27. */
    
28. 
    
29. *****航天  140301 ;
    
30. %let date_str = '20140301';
    
31. proc sql;
    
32.   connect to oracle as conname  (&risk);  
    
33.   create table jingzhi as select * from connection to conname   
    
34. (
    
35. select bizdate
    
36.      , avg(add_nav) as add1
    
37.   from (
    
38.         select f16_1090            as seccode,
    
39.                ob_object_name_1090 as sec_name,
    
40.                OB_OBJECT_NAME_1018 as com_name,            /* wind 取复权净值*/
    
41.                F4_1101             as nav,
    
42.                F21_1101            as add_nav,                              
    
43.                F13_1101            as bizdate
    
44.           from TB_OBJECT_1101
    
45.           left join TB_OBJECT_1090 on F14_1101 = F2_1090
    
46.           left join TB_OBJECT_1099 on F14_1101 = F1_1099
    
47.           left join tb_object_1018 on F12_1099 = F34_1018
    
48.          where f13_1101 between  '20140301' and  &date_str
    
49.            and f16_1090 in ('00053')   
    
50.        )
    
51. group by bizdate
    
52. order by bizdate
    
53. );
    
54. disconnect from conname;
    
55. quit;
    
56. 
    
57. data jingzhi;
    
58. set jingzhi;
    
59.     if _N_=1 then call symput('a',add1);
    
60. run;
    
61. data jingzhi;
    
62.     set jingzhi;
    
63.     add_r=add1/&a-1;
    
64.     nav=add1;
    
65. run;
    
66. data jingzhi;
    
67.     set jingzhi;
    
68. proc sort;
    
69. by bizdate;
    
70. run;
    
71. proc sql;
    
72.   connect to oracle as conname  (&risk);  
    
73.   create table pf_all_bench16  as select * from connection to conname   
    
74. (
    
75.    select bizdate
    
76.         , bench
    
77.         , sec_name as panamecn  
    
78.      from zx_wind_bench  
    
79.     where seccode='000535'  
    
80.       and bizdate between  '20140311'   and &date_str
    
81.     order by bizdate
    
82. );
    
83. disconnect from conname;
    
84. quit;
    
85. data pf_all_bench16;
    
86.     set pf_all_bench16;
    
87.     if _N_=1 then call symput('a',bench);
    
88. run;
    
89. data pf_all_bench16;
    
90.     set pf_all_bench16;
    
91.     r=bench/&a-1;
    
92.     p='R000003';
    
93. run;
    
94. proc sort;
    
95.     by bizdate;
    
96. run;
    
97. 
    
98. data pf_all_bench16;
    
99.     merge pf_all_bench16 (in=a)  jingzhi(in=b);
    
100.     by  bizdate;
     
101.     if a and b;
     
102. run;
     
103. 
     
104. proc sql;
     
105.   create table pf_all_bench17  as select * from
     
106. (
     
107.    select p
     
108.         , panamecn
     
109.         , bizdate
     
110.         , nav
     
111.         , add1
     
112.         , add_r
     
113.         , r
     
114.      from pf_all_bench16
     
115. );
     
116. 
     
117. quit;
     
118. 
     
119. proc sql;
     
120.   delete from  fkdb.pf_all_bench  where  p='R000003' and bizdate= &date_str;
     
121.   insert into  fkdb.pf_all_bench  select * from pf_all_bench17 where bizdate= &date_str;
     
122. quit;

复制代码

这段代码写的真不怎么有效率，
看得头疼。

我去摘点车厘子，等会儿上传结果。

这个就是一个简单的数据操作和数据转换，太简单了，实在写不下去了。。。谁给你接着写吧。

1. /* Proc SQL start*/
   
2. proc sql;
   
3.         /*创建数据链接到Oracle并且命名为conname，具体的用户名，密码参数都在risk里面存着。*/
   
4.   connect to oracle as conname  (&risk);  
   
5. 
   
6.   /*从conname选择所有的数据命名为temp*/
   
7.   create table temp as select * from connection to conname   
   
8. 
   
9. (
   
10. /*选择 某个交易日，交易代码，交易商品名称，当日成交价，交易日@2012-12-03，交易日@2012-12-03成交价，交易价格浮动百分比 并且命名为"r"，某个交易日和2012-12-03之间的天数间隔,命名为“interval”*/
    
11. select bizdate,a.code,a.name,close_price,bizdate2,close_price2,close_price/close_price2-1 as r, to_date(bizdate,'yyyy-mm-dd')-to_date(bizdate2,'yyyy-mm-dd') as interval
    
12. 
    
13. from
    
14. 
    
15. /*把下面两个表以某个交易日的数据为主，然后把2012-12-03那天的交易数据以交易代码为关键键对应的水平合并过来。 合成的表座位上面的源表*/
    
16. 
    
17. 
    
18. /*交易类型代码为S, 交易代码在以下数字里面，选择某个交易日(&date_str),交易代码，交易商品名称，当日成交价*/
    
19. ( select bizdate,code,name,close_price
    
20.   from pf_all_quotation
    
21.   where typecode = 'S' and code in ('81699', '00030', '39992', '81602', '00001', 'h1100', 'h1100','h1100', 'h1100','00093', 'h1100', 'h1100', '81602', '00090', '00090') and bizdate = &date_str ) a
    
22.    
    
23. left join
    
24. 
    
25. /*交易类型代码为S, 交易代码在以下数字里面，选择2012-12-03交易日，交易代码，交易商品名称，2012-12-03成交价*/
    
26. ( select bizdate as bizdate2,code,name,close_price as close_price2
    
27.   from pf_all_quotation
    
28.   where typecode = 'S'  and code in ('81699', '00030', '39992', '81602', '00001', 'h1100', 'h1100','h1100', 'h1100','00093', 'h1100', 'h1100', '81602', '00090', '00090') and bizdate = 2012123  ) b
    
29. 
    
30. /*关键键位是 交易代码*/
    
31. on a.code=b.code
    
32. );
    
33. /*断开到conname的链接*/
    
34. disconnect from conname;
    
35. quit;
    
36. 
    
37. 
    
38. 
    
39. 
    
40. 
    
41. %let a='R000001';   * 成长;
    
42. 
    
43. /*生成两个新的列，一个是r1,是新的交个变动率，对两个交易代码81699&81602的变动率做降权(标准化，减低比重。)，生成一个列是p作为组,那两个交易代码的p值R000001的字符，他们可以被认为是R000001组的*/
    
44. data temp1;
    
45.         set temp;
    
46. 
    
47. /*如果交易代码是81699，那么新的交易价格变动率是原来的80%, p是R000001*/
    
48. if code='81699' then
    
49.   do;
    
50.   r1=0.8*r;
    
51.   p=&a;         
    
52.   end;
    
53.   /*如果交易代码是81602，那么新的交易价格变动率是原来的20%, p是R000001*/
    
54. if code='81602' then
    
55.   do;
    
56.   r1=0.2*r;
    
57.   p=&a;
    
58.   end;
    
59.   /*其他的股票代码，新的交个变动率是0， p是空值。*/
    
60. run;
    
61. 
    
62. /*计算组别R000001的新的价格变动率（被降权后的变动率）的和（总值）*/
    
63. proc sql;
    
64.         create table temp11 as
    
65. ( select sum(r1)as r,p
    
66.   from temp1
    
67.   where p=&a
    
68.   group by p
    
69. );
    
70. quit;
    
71. 
    
72. 
    
73. %let i1=0.03;
    
74. %let tax=0;
    
75. %let i=0.007;
    
76. %let i_d=0.065;
    
77. %let i7=0.013;
    
78. 
    
79. /*生成两个新的列，一个是r1,是新的交个变动率，对两个交易代码00030&00001的变动率做降权(标准化，减低比重。)，生成一个列是p作为组,那两个交易代码的p值R000002的字符，他们可以被认为是R000002组的*/
    
80. %let a='R000002';    * 同心;
    
81. data temp5;
    
82. set temp;
    
83. /*如果交易代码是00030，那么新的交易价格变动率是原来的65%, p是R000002*/
    
84. if code='00030' then
    
85.   do;
    
86.   r1=0.65*r;
    
87.   p=&a;         
    
88.   end;
    
89. /*如果交易代码是00001，那么新的交易价格变动率是原来的30%, p是R000002*/
    
90. if code='00001' then
    
91.   do;
    
92.   r1=0.3*r;
    
93.   p=&a;
    
94.   end;
    
95. /*如果交易代码是00001，那么新的交易价格变动率是=天数间隔*（1-0）*0.03、365*0.05=天数间隔*0.5475=（str_date-2012-12-03）*0.5475, p是R000002*/
    
96. if code='00090' then
    
97.   do;
    
98.   r1=interval*(1-&tax)*&i1/365*0.05;
    
99.   p=&a;
    
100.   end;
     
101. run;
     
102. 
     
103. 
     
104. /*#################################################################################################################*/
     
105. /*#                              跟上面的一样只不过是不同的交易代码的集合并且新的开始日期是2012-03-02                      #*/
     
106. /*################################################################################################################*/
     
107. 
     
108. /*新的一只*/
     
109. proc sql;
     
110. 
     
111. connect to oracle as conname  (&risk);  
     
112. 
     
113. create table temp as select * from connection to conname   
     
114. (
     
115. select bizdate,a.code,a.name,close_price,bizdate2,close_price2,close_price/close_price2-1 as r, to_date(bizdate,'yyyy-mm-dd')-to_date(bizdate2,'yyyy-mm-dd') as interval      
     
116. 
     
117. from
     
118. 
     
119. (
     
120. select bizdate,code,name,close_price
     
121. from pf_all_quotation
     
122. where typecode = 'S' and code in ('81699', '00030', '39992', '81602', '00001', 'h1100', 'h1100','h1100', 'h11008','000935', 'h1100', 'h1100', '81602', '00090', '00090')  and bizdate = &date_str ) a
     
123.    
     
124. left join
     
125. 
     
126. (
     
127. select bizdate as bizdate2,code,name,close_price as close_price2
     
128. from pf_all_quotation
     
129. where typecode = 'S' and code in ('81699', '00030', '39992', '81602', '00001', 'h1100', 'h1100','h1100', 'h1100','00093', 'h1100', 'h1100', '81602', '00090', '00090') and bizdate = 2012032  ) b
     
130. 
     
131. on a.code=b.code
     
132. 
     
133. );
     
134. disconnect from conname;
     
135. quit;
     
136. 
     
137. 
     
138. %let a='R000003';   
     
139. data temp9;
     
140. set temp;
     
141. if code='00093' then
     
142.   do;
     
143.   r1=0.8*r;
     
144.   p=&a;         
     
145.   end;
     
146. if code='h1100' then
     
147.   do;
     
148.   r1=0.2*r;
     
149.   p=&a;
     
150.   end;
     
151. run;
     
152. 
     
153. proc sql;
     
154. create table temp99 as
     
155. ( select sum(r1)as r,p
     
156.   from temp9
     
157.   where p=&a
     
158.   group by p
     
159.   );
     
160. /*#################################################################################################################*/
     
161. /*#                                                                                                              #*/
     
162. /*################################################################################################################*/
     
163. 
     
164. 
     
165. proc sql;
     
166. connect to oracle as conname  (&risk);  
     
167. 
     
168. create table jingzhi2 as
     
169. 
     
170. select *
     
171. from connection to conname   
     
172. (
     
173. select c.*,b2,nav2,add2
     
174. 
     
175. from
     
176. 
     
177. (select a.painnercode as p, panamecn, bizdate, nav, addupnav as add1
     
178. from pd_real_pa_netval a
     
179. left join
     
180. pd_pa_definition b
     
181. on a.painnercode = b.painnercode
     
182. where bizdate = &date_str and a.painnercode ='R000003' ) c
     
183. left join     
     
184. 
     
185. (select painnercode as p,bizdate as b2, nav as nav2, addupnav as add2
     
186. from  pd_real_pa_netval
     
187. where bizdate = 2012032 and painnercode ='R000003' ) d  on c.p=d.p
     
188. );
     
189. 
     
190. disconnect from conname;
     
191. quit;
     
192. 
     
193. 
     
194. data jingzhi2;
     
195. set jingzhi2;
     
196. add_r=(add1-add2)/nav2;      * 修正复权净值增长率;
     
197. drop b2 nav2 add2;
     
198. run;
     
199. 
     
200. data pf_all_bench2;      * 业绩基准表;
     
201. merge jingzhi2 temp99;
     
202. by p;
     
203. run;
     
204. 
     
205. =============================最后部分
     
206. 
     
207. *****航天  140301 ;
     
208. %let date_str = '20140301';
     
209. proc sql;
     
210.   connect to oracle as conname  (&risk);  
     
211.   create table jingzhi as select * from connection to conname   
     
212. (
     
213.       select bizdate, avg(add_nav) as add1 from (
     
214. select f16_1090            as seccode,
     
215.        ob_object_name_1090 as sec_name,
     
216.        OB_OBJECT_NAME_1018 as com_name,            /* wind 取复权净值*/
     
217.        F4_1101             as nav,
     
218.        F21_1101            as add_nav,                              
     
219.        F13_1101            as bizdate
     
220.   from TB_OBJECT_1101
     
221.   left join TB_OBJECT_1090 on F14_1101 = F2_1090
     
222.   left join TB_OBJECT_1099 on F14_1101 = F1_1099
     
223.   left join tb_object_1018 on F12_1099 = F34_1018
     
224. where f13_1101 between  '20140301'   and  &date_str
     
225. and f16_1090 in ('00053')   )
     
226. group by bizdate
     
227. order by bizdate
     
228. );
     
229.   disconnect from conname;
     
230. quit;
     
231. 
     
232. data jingzhi;
     
233. set jingzhi;
     
234. if _N_=1 then call symput('a',add1);
     
235. run;
     
236. data jingzhi;
     
237. set jingzhi;
     
238. add_r=add1/&a-1;
     
239. nav=add1;
     
240. run;
     
241. data jingzhi;
     
242. set jingzhi;
     
243. proc sort;
     
244. by bizdate;
     
245. run;
     
246. proc sql;
     
247.   connect to oracle as conname  (&risk);  
     
248.   create table pf_all_bench16  as select * from connection to conname   
     
249. (
     
250.    select  bizdate,bench,sec_name as panamecn  from zx_wind_bench  where seccode='000535'  and bizdate between  '20140311'   and  &date_str order by bizdate
     
251. );
     
252.   disconnect from conname;
     
253. quit;
     
254. data pf_all_bench16;
     
255. set pf_all_bench16;
     
256. if _N_=1 then call symput('a',bench);
     
257. run;
     
258. data pf_all_bench16;
     
259. set pf_all_bench16;
     
260. r=bench/&a-1;
     
261. p='R000003';
     
262. run;
     
263. proc sort;
     
264. by bizdate;
     
265. run;
     
266. 
     
267. data pf_all_bench16;
     
268. merge pf_all_bench16 (in=a)  jingzhi(in=b);
     
269. by  bizdate;
     
270. if a and b;
     
271. run;
     
272. 
     
273. proc sql;
     
274.   create table pf_all_bench17  as select * from
     
275. (
     
276.    select p,panamecn,bizdate,nav,add1,add_r,r from pf_all_bench16
     
277. );
     
278. 
     
279. quit;
     
280. 
     
281. proc sql;
     
282.   delete from  fkdb.pf_all_bench  where  p='R000003' and bizdate= &date_str;
     
283.   insert into  fkdb.pf_all_bench  select * from pf_all_bench17 where bizdate= &date_str;
     
284. quit;
     
285. 
     
286. 
     
287. 
     
288. 
     
289. /*
     
290. data pf_all_bench;
     
291. set fkdb.pf_all_bench;
     
292. *if not missing(bizdate);
     
293. run;
     
294. data fkdb.pf_all_bench;
     
295. set pf_all_bench;
     
296. run;
     
297. 
     
298. proc sql;
     
299.    delete from  fkdb.pf_all_bench  where bizdate = &date_str;
     
300.    insert into  fkdb.pf_all_bench  select * from pf_all_bench;
     
301. quit;
     
302. 
     
303. data pf_all_bench;
     
304. set fkdb.pf_all;
     
305. if not missing(bizdate);
     
306. if not missing(panamecn);
     
307. run;
     
308. proc sql;
     
309. drop table fkdb.pf_all_bench;
     
310. quit;
     
311. data fkdb.pf_all_bench;
     
312. set pf_all_bench;
     
313. run;   */

复制代码

zhangwenteng 发表于 2014-5-14 10:41
这个就是一个简单的数据操作和数据转换，太简单了，实在写不下去了。。。谁给你接着写吧。

一句一句解释人都会疯掉～～～

zhangwenteng 发表于 2014-5-14 10:41
这个就是一个简单的数据操作和数据转换，太简单了，实在写不下去了。。。谁给你接着写吧。

一句一句解释人都会疯掉～～～

zhangwenteng 发表于 2014-5-14 10:41
这个就是一个简单的数据操作和数据转换，太简单了，实在写不下去了。。。谁给你接着写吧。

一句一句解释人都会疯掉～～～

zhangwenteng 发表于 2014-5-14 10:41
这个就是一个简单的数据操作和数据转换，太简单了，实在写不下去了。。。谁给你接着写吧。

大神好答！

“生成一个列是p作为组,那两个交易代码的p值R000001的字符，他们可以被认为是R000001组的”请问这句是什么意思啊？你好像字没打全。还有请问为什么要做降权处理呢？

关于R000002的第二部分代码好像有错误。有两个code好像写错了。