DN: Factoring out the month function cut the time in half! However, rather than use your suggestion of proc summary and transpose, I did the same thing using proc sql to create a macro variable. The only difference is with respect to the ordering of the output variables.
: The current version of the code, as shown below, took 75 seconds cpu time and just under 5 minutes real time for 50 million records. I speeded up the proc sql part by limiting it to only analyzing the first 1,000 records. That number would have to be increased if all 12 months don't exist somewhere in the first 1,000 records:
/* Create some test data */
/* Note: Every other record in the dataset is missing values for one month */
data have (drop=months);
format id best32.;
input date ind1-ind4 ;
informat date date9.;
format date yymon7.;
other_variable=2;
do id=1 to 5000000;
date="01dec2010"d;
do months=1 to 12;
date=intnx('month',date,1);
if not(months eq 9 and mod(id,2)) then output;
end;
end;
cards;
01dec2010 1 2 3 4
;
/* Create a macro variable that specifies the desired variable names after transposing */
/* Note: the &junk variable is only created so that proc sql doesn't return a warning */
proc sql noprint;
select distinct 'ind1_'||put(date,yymon7.)||
' ind2_'||put(date,yymon7.)||
' ind3_'||put(date,yymon7.)||
' ind4_'||put(date,yymon7.),
date
into :vars separated by " ",
:junk
from have (obs=1000) /*must be big enough to capture all months*/
order by date
;
quit;
data want;
set have;
by id;
array want(*) &vars.;
retain want;
if first.id then call missing(of want(*));
_n_=month(date);
want((_n_-1)*4+1)=ind1;
want((_n_-1)*4+2)=ind2;
want((_n_-1)*4+3)=ind3;
want((_n_-1)*4+4)=ind4;
drop date ind1-ind4;
if last.id then output;
run;
Click image to register for webinar
