this is one way you can do it. data test; Var1 = 123456; Date1 = '1JAN2011'd; Date2 = '1JUN2011'd; Var1_1 = 1; Var1_2 = 2; Var1_3 = 3; Var1_4 = 4; Var1_5 = 5; Var1_6 = 6; Var1_7 = 7; Var1_8 = 8; Var1_9 = 9; Var1_10 = 10; Var1_11 = 11; Var1_12 = 12; run; options symbolgen mprint merror mlogic; %macro shift; %* Get the list of the variables to be shifted; proc contents data=test out=name(keep=name where=(name not in ('Var1','Date1','Date2'))); run; %* Store each variable into a macro variable; data _null_; set name nobs=cnt; call symput(compress('name'||put(_n_,best.)),name); if _n_ =cnt then call symputx('cnt',_n_); run; %* Create a macro variable storing the date difference; data _null_; set test nobs=dt_cnt; x=intck('month',date1,date2); call symput(compress('dt_dif'||put(_n_,best.)),x); if _n_ =dt_cnt then call symputx('dt_cnt',_n_); run; %* Create the new dataset with the shift; data new; set test; %do j=1 %to &dt_cnt; %do i=1 %to &&dt_dif&j; &&name&i = 0; %end; %do i=&cnt %to %eval(&&dt_dif&j+1) %by -1; &&name&i = &&name&i - &&&dt_dif&j; %end; output; %end; run; %mend; %shift
... View more