求助。用sas计算每个月的市净率

已知（1）市净率每季度更新一次；
       （2）市净率的每月更新公式=（本月末股票总市值/上季末股票总市值）*市净率；季末股票总市值为该季度最后一个月的月末股票总市值； （即上季末股票总市值为上一季度结束月份的股票的总市值）
       （3）下列数据中，stkcd为股票代码，pb为市净率，msmvttl为月末股票总市值；
       （4）如数据集所示，12月1月2月为第一个季度3月4月5月为第二个季度6月7月8月为第三个季度9月10月11月为第四个季度；
data a;
input stkcd date msmvttl pb;
cards;
1 199312 6000 2
1 199401 6300 2
1 199402 6200 2
1 199403 6000 3
1 199404 6400 3
1 199405 6700 3
1 199406 7000 5
1 199407 6500 5
1 199408 6800 5
1 199409 7100 2
1 199410 6750 2
1 199411 6000 2
1 199412 6000 2
1 199501 6300 2
1 199502 6200 2
1 199503 6000 3
1 199504 6400 3
1 199505 6700 3
1 199506 7000 5
1 199507 6500 5
1 199508 6800 5
1 199509 7100 2
1 199510 6750 2
1 199511 6000 2
3 199312 6000 2
3 199401 6300 2
3 199402 6200 2
3 199403 6000 3
3 199404 6400 3
3 199405 6700 3
3 199406 7000 5
3 199407 6500 5
3 199408 6800 5
3 199409 7100 2
3 199410 6750 2
3 199411 6000 2
3 199412 6000 2
3 199501 6300 2
3 199502 6200 2
3 199503 6000 3
3 199504 6400 3
3 199505 6700 3
3 199506 7000 5
3 199507 6500 5
3 199508 6800 5
3 199509 7100 2
3 199510 6750 2
3 199511 6000 2
;
run;

试了一下，供参考：

proc sort data=a;by stkcd date;run;

data b;
        set a;
        by stkcd;
        retain jm;
        if first.stkcd then jm=0;
        if substr(put(date,best6.),5,2) in ("02","05","08","11") then jm=msmvttl;
        jm1=lag(jm);
        if jm=0 then jm1=0;
        if jm1^=0 then gx=(msmvttl/jm1)*pb;
run;

1. data a;
   
2. input stkcd date msmvttl pb;
   
3. cards;
   
4. 1 199312 6000 2
   
5. 1 199401 6300 2
   
6. 1 199402 6200 2
   
7. 1 199403 6000 3
   
8. 1 199404 6400 3
   
9. 1 199405 6700 3
   
10. 1 199406 7000 5
    
11. 1 199407 6500 5
    
12. 1 199408 6800 5
    
13. 1 199409 7100 2
    
14. 1 199410 6750 2
    
15. 1 199411 6000 2
    
16. 1 199412 6000 2
    
17. 1 199501 6300 2
    
18. 1 199502 6200 2
    
19. 1 199503 6000 3
    
20. 1 199504 6400 3
    
21. 1 199505 6700 3
    
22. 1 199506 7000 5
    
23. 1 199507 6500 5
    
24. 1 199508 6800 5
    
25. 1 199509 7100 2
    
26. 1 199510 6750 2
    
27. 1 199511 6000 2
    
28. 3 199312 6000 2
    
29. 3 199401 6300 2
    
30. 3 199402 6200 2
    
31. 3 199403 6000 3
    
32. 3 199404 6400 3
    
33. 3 199405 6700 3
    
34. 3 199406 7000 5
    
35. 3 199407 6500 5
    
36. 3 199408 6800 5
    
37. 3 199409 7100 2
    
38. 3 199410 6750 2
    
39. 3 199411 6000 2
    
40. 3 199412 6000 2
    
41. 3 199501 6300 2
    
42. 3 199502 6200 2
    
43. 3 199503 6000 3
    
44. 3 199504 6400 3
    
45. 3 199505 6700 3
    
46. 3 199506 7000 5
    
47. 3 199507 6500 5
    
48. 3 199508 6800 5
    
49. 3 199509 7100 2
    
50. 3 199510 6750 2
    
51. 3 199511 6000 2
    
52. ;
    
53. run;
    
54. 
    
55. data b;
    
56.     set a;
    
57.     year=input(substr(strip(put(date,best.)),1,4),best.);
    
58.     month=input(substr(strip(put(date,best.)),5,2),best.);
    
59.     if month=12 then do;
    
60.         year=year+1;
    
61.         month=0;
    
62.     end;
    
63. run;
    
64. 
    
65. proc sort data=b;
    
66.     by year;
    
67. run;
    
68. 
    
69. data _null_;
    
70.     set b;
    
71.     by year;
    
72.     if _n_=1 then call symput("miny",strip(put(year,best.)));
    
73. run;
    
74. 
    
75. data c;
    
76.     set b;
    
77.     if 0<=month<=2 then id=(year-&miny.)*4+1;
    
78.     else if 3<=month<=5 then id=(year-&miny.)*4+2;
    
79.     else if 6<=month<=8 then id=(year-&miny.)*4+3;
    
80.     else if 9<=month<=11 then id=(year-&miny.)*4+4;
    
81. run;
    
82. 
    
83. 
    
84. proc sql noprint;
    
85.     create table d as
    
86.     select s1.stkcd
    
87.           ,s1.date
    
88.           ,s1.msmvttl
    
89.           ,s1.pb
    
90.           ,(s1.msmvttl/s2.msmvttl)*s1.pb as newpb
    
91.     from c as s1
    
92.     left join c(where=(month in (2 5 8 11))) as s2
    
93.     on s1.stkcd=s2.stkcd and s1.id=s2.id+1
    
94.     ;
    
95. quit;

复制代码

为了这100金币，折腾了一下午，总算是把程序调试出来了。楼上的两个帖子的CODE完全不行，没那么简单。加了两个附件： 名字都是STOCK, 一个是SAS源程序（把后缀名改为.sas就可以运行），一个是运行结果（excel 文件）。半天时间的劳动，希望楼主信守承诺。我还指望着用这金币买书呢。如果有任何问题，站内联系。谢谢。以下是程序：



****author: thinman66*****************;
****date:   9/3/2013******************;
****purpose: earning 100 currencies on bbs.pingu.org******************;

data stock;
input stkcd date $  msmvttl pb;
cards;
1 199312 6000 2
1 199401 6300 2
1 199402 6200 2
1 199403 6000 3
1 199404 6400 3
1 199405 6700 3
1 199406 7000 5
1 199407 6500 5
1 199408 6800 5
1 199409 7100 2
1 199410 6750 2
1 199411 6000 2
1 199412 6000 2
1 199501 6300 2
1 199502 6200 2
1 199503 6000 3
1 199504 6400 3
1 199505 6700 3
1 199506 7000 5
1 199507 6500 5
1 199508 6800 5
1 199509 7100 2
1 199510 6750 2
1 199511 6000 2
3 199312 6000 2
3 199401 6300 2
3 199402 6200 2
3 199403 6000 3
3 199404 6400 3
3 199405 6700 3
3 199406 7000 5
3 199407 6500 5
3 199408 6800 5
3 199409 7100 2
3 199410 6750 2
3 199411 6000 2
3 199412 6000 2
3 199501 6300 2
3 199502 6200 2
3 199503 6000 3
3 199504 6400 3
3 199505 6700 3
3 199506 7000 5
3 199507 6500 5
3 199508 6800 5
3 199509 7100 2
3 199510 6750 2
3 199511 6000 2
;
run;

data stock;
set stock;
year=input(substr(date, 1, 4), ??best.);
month=input(substr(date, 5, 2), ??best.);
if month in (1, 2, 3) then quarter=1;
else if month in (4,5,6) then quarter=2;
else if month in (7,8,9) then quarter=3;
else if month in (10,11,12) then quarter=4;
run;

data stock;
set stock;
if month=3 then endmv1=msmvttl;
else if month=6 then endmv2=msmvttl;
else if month=9 then endmv3=msmvttl;
else if (month=11 or month=12) then endmv4=msmvttl;
run;

proc sort data=stock ;
by stkcd year month;
run;
data yr93;
set stock;
if year=1993 ;
run;

data yr94;
set stock;
if year=1994 ;
run;

data yr95;
set stock;
if year=1995 ;
run;



Data yr94( Drop = _: ) ;
Set yr94 ( Rename = ( endmv1 = _endmv1 endmv2 = _endmv2   endmv3 = _endmv3 endmv4 = _endmv4  ) ) ;
Retain endmv1 endmv2 endmv3 endmv4 ;

If not missing( _endmv1   ) then endmv1   = _endmv1 ;
If not missing( _endmv2 ) then endmv2 = _endmv2;
If not missing( _endmv3   ) then endmv3   = _endmv3;
If not missing( _endmv4 ) then endmv4 = _endmv4;

run ;

Data yr95( Drop = _: ) ;
Set yr95 ( Rename = ( endmv1 = _endmv1 endmv2 = _endmv2   endmv3 = _endmv3 endmv4 = _endmv4  ) ) ;
Retain endmv1 endmv2 endmv3 endmv4 ;

If not missing( _endmv1   ) then endmv1   = _endmv1 ;
If not missing( _endmv2 ) then endmv2 = _endmv2;
If not missing( _endmv3   ) then endmv3   = _endmv3;
If not missing( _endmv4 ) then endmv4 = _endmv4;

run ;

proc sort data=yr94;
by         descending endmv1  descending endmv2 descending endmv3 descending endmv4 ;
run;
proc sort data=yr95;
by         descending endmv1  descending endmv2 descending endmv3 descending endmv4 ;
run;

Data yr94( Drop = _: ) ;
Set yr94 ( Rename = ( endmv1 = _endmv1 endmv2 = _endmv2   endmv3 = _endmv3 endmv4 = _endmv4  ) ) ;
Retain endmv1 endmv2 endmv3 endmv4 ;

If not missing( _endmv1   ) then endmv1   = _endmv1 ;
If not missing( _endmv2 ) then endmv2 = _endmv2;
If not missing( _endmv3   ) then endmv3   = _endmv3;
If not missing( _endmv4 ) then endmv4 = _endmv4;

run ;

Data yr95( Drop = _: ) ;
Set yr95 ( Rename = ( endmv1 = _endmv1 endmv2 = _endmv2   endmv3 = _endmv3 endmv4 = _endmv4  ) ) ;
Retain endmv1 endmv2 endmv3 endmv4 ;

If not missing( _endmv1   ) then endmv1   = _endmv1 ;
If not missing( _endmv2 ) then endmv2 = _endmv2;
If not missing( _endmv3   ) then endmv3   = _endmv3;
If not missing( _endmv4 ) then endmv4 = _endmv4;

run ;



data combine;
set yr93 yr94 yr95;
run;

data combine;
set combine;
if quarter=4 then lastq=endmv3;
else if quarter=3 the lastq=endmv2;
else if quarter=2 the lastq=endmv1;
run;


data combine (drop=endmv1-endmv4);
set combine;
if (year=1994 and quarter=1) or (year=1995 and quarter=1) then lastq=endmv4;
***sjlv is         市净率        ***********************;

sjlv=( msmvttl/lastq)*pb;
run;