: Given the size of your file, I thought this might be a good time to introduce (and, hopefully, get some feedback regarding) a concept that KSharp came up with and he and I are formulating for a future conference paper, namely using a data step to accomplish such transpose tasks.
Here is an example using a file like yours, although I've only tested it with 1.2 million and 12 million records. With 1.2 million records the data step method runs more than 10 times faster than using proc transpose. With 12 million records, the difference amplified to 15 times faster and I presume that will continue to increase with even larger files like yours.
Since both this and 's proposed code require that the data be sorted, I didn't include those times in the comparison.
data have ;
length id 8.;
input date ind1-ind4 ;
informat date date9.;
format date yymon7.;
do id=1 to 100000;
output;
end;
cards;
01jan2011 1 2 3 4
01feb2011 5 6 7 8
01mar2011 9 1 2 3
01apr2011 4 5 6 7
01may2011 1 2 3 4
01jun2011 5 6 7 8
01jul2011 9 1 2 3
01aug2011 4 5 6 7
01SEP2011 1 2 3 4
01OCT2011 5 6 7 8
01NOV2011 9 1 2 3
01DEC2011 4 5 6 7
;
proc sort data=have;
by id date;
run;
/*transpose long to wide using data step*/
proc sql;
create table temp as
select distinct date from have
;
quit;
data _null_;
set temp end=last;
if _n_ eq 1 then call execute('data want; merge ');
call execute(catt('have(where=(date=',date,')',
' rename=( ind1=ind1_',left(put(date,yymon7.)),
' ind2=ind2_',left(put(date,yymon7.)),
' ind3=ind3_',left(put(date,yymon7.)),
' ind4=ind4_',left(put(date,yymon7.)),'))'));
if last then call execute('; by id; drop date; run;');
run;
/*compare with transpose using proc transpose*/
proc transpose data=have out=tall ;
by id date;
var ind1-ind4;
run;
proc transpose data=tall out=want delimiter=_;
by id;
id _name_ date;
var col1;
run;
