Hello, Wondering what others have done in a similar situation - I have a long dataset with account numbers and monthly performance data, which I need to convert into a wide dataset (in essence appending subsequent months' performance data to the previous month's record). I do this by first putting together the monthly files into 1 large dataset (the Month0 dataset below) and doing a left join on this dataset. The Month0 dataset has millions of rows. Are there any SAS options to improve the efficiency of this query, or are there modifications to this query anyone can suggest? Thanks! %macro BuildLookouts(begdate, enddate); %let start=%sysfunc(inputn(&begdate,anydtdte9.)); %let end=%sysfunc(inputn(&enddate,anydtdte9.)); /*determines the number of months between the two dates*/ %let dif=%sysfunc(intck(month,&start,&end)); %put dif=&dif.; %do i=1 %to &dif; proc sql; create table Lookout_Month&i as select a.* ,b.perf_month_month&i ,b.perf_MTD_month&i from Lookout_Month%eval(&i-1) a left join Lookout_Month0(rename=( perf_month = perf_month_month&i perf_MTD = perf_MTD_month&i )) b on a.acct_number = b.acct_number and intnx('month',a.perf_month,&i,'e') = b.perf_month_month&i ; quit; %end; %mend; %BuildLookouts(31JAN2016, 31DEC2017);
... View more