SAS 简单的宏编写

    作为SAS初学者，在遇到具体的问题时，真是想不到一些比较好的解决办法，幸好有同事帮忙。最近遇到个问题，具体内容是A表中有（item_id,key,val_z,vla_m,val,date）,其中date格式为‘201001’，date范围是‘201001-201009’，而val=val_z/val_m,要求计算每连续三个月的val的值，然后从7（因为是9个月，所以最终得到7个值）个val中取出一个最大值作为最后结果。

    具体编码如下：

data sumcnad01;                                                                                                                                    
set sub_jhcb.sumcnad01;                                                                                                                            
where date_t>='201001' and date_t<='201009';                                                                                                       
run;                                                                                                                                               
                                                                                                                                                   
                                                                                                                                                   
data temp1(keep=item_id jhz_key val_z val_m date_t);                                                                                               
set sumcnad01;                                                                                                                                     
run;                                                                                                                                               
                                                                                                                                                   
data tmpresult(keep=item_id jhz_key val);                                                                                                          
set sumcnad01;                                                                                                                                     
where 1<>1;                                                                                                                                        
run;                                                                                                                                               
                                                                                                                                                   
/*计算201001-201009*/                                                                                                                              
%macro best;                                                                                                                                       
%let startmonth=200912;                                                                                                                            
                                                                                                                                                   
%do i=1 %to 7;                                                                                                                                     
data _null_;                                                                                                                                       
     call symput("startmonth",substr(compress(put(intnx('month',input("&startmonth"||'01',yymmdd10.),+1),yymmdd10.),'-'),1,6));                    
run;                                                                                                                                               
data _null_;                                                                                                                                       
     call symput("endmonth",substr(compress(put(intnx('month',input("&startmonth"||'01',yymmdd10.),+3),yymmdd10.),'-'),1,6));                      
run;                                                                                                                                               
                                                                                                                                                   
proc sql;                                                                                                                                          
      create table temp2 as                                                                                                                        
      select distinct item_id,jhz_key,(sum(Val_z)/sum(Val_m)) as Val                                                                               
      from temp1                                                                                                                                   
      where date_t >= "&startmonth" and date_t <= "&endmonth"                                                                                      
      group by item_id,jhz_key;                                                                                                                    
quit;                                                                                                                                              
                                                                                                                                                   
                                                                                                                                                   
proc append base=tmpresult data=temp2 force;quit;                                                                                                  
                                                                                                                                                   
%end;                                                                                                                                              
                                                                                                                                                   
%mend best;                                                                                                                                        
                                                                                                                                                   
%best;                                                                                                                                             
                                                                                                                                                   
proc sql;                                                                                                                                          
create table result as                                                                                                                             
select item_id,jhz_key,max(val) as bestval                                                                                                         
from  tmpresult                                                                                                                                    
group by item_id,jhz_key;                                                                                                                          
quit;                              
 

 通过此次编码，了解了SAS宏编程的初步知识，也对intnx这个函数有了一定的理解。