topic Re: Transpose in SAS Programming

Transpose

SASPhile — Mon, 14 Jan 2013 03:31:01 GMT

There are 700 million records. The columns are to transformed to this way:
id           date                         ind1   ind2   ind3   ind4
18010    Sep 1 2011                   0      0       0      0
18010   Oct 1 2011                       0       0       0     0
18010   Nov 1 2011                      0      0       0      0
18010   Dec 1 2011                       1      0       0      0

id ind1_sep2011 ind1_oct2011 ind1_nov2011 ind1_dec2011
18010 0 0 0 1

For display purposes I just showed only ind1.What is the most efficient way to achive this format?

Re: Transpose

art297 — Mon, 14 Jan 2013 04:43:27 GMT

Are you sure that is what you want?

I would think that you would want something like:

id ind1 ind2 ind3 ind4

18010 1Dec2011 . . .

If not, aren't you also interested in the other three INDs and are the dates shown the only ones in your data?

Re: Transpose

SASPhile — Mon, 14 Jan 2013 05:00:35 GMT

There are fout indicators ind1--ind4 and there are 12 months.Sep2011--sep2012.All the dat columns have to be transposed thus making 48 columns plus id. Ind1 for sep2011 will be Ind_sep2011 ,ind1 for oct2011 will be ind1_oct2011 ...ind4_sep2012.

Re: Transpose

Tom — Mon, 14 Jan 2013 13:38:48 GMT

Double transpose would seem the easiest way.

data have ;

input id date ind1-ind4 ;

informat date date9.;

format date yymon7.;

cards;

18010 01SEP2011 1 2 3 4

18010 01OCT2011 5 6 7 8

18010 01NOV2011 9 1 2 3

18010 01DEC2011 4 5 6 7

;

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;

data _null_; set;

put (_ALL_) (=/);

run;

id=18010

_NAME_=COL1

ind1_2011SEP=1

ind2_2011SEP=2

ind3_2011SEP=3

ind4_2011SEP=4

ind1_2011OCT=5

ind2_2011OCT=6

ind3_2011OCT=7

ind4_2011OCT=8

ind1_2011NOV=9

ind2_2011NOV=1

ind3_2011NOV=2

ind4_2011NOV=3

ind1_2011DEC=4

ind2_2011DEC=5

ind3_2011DEC=6

ind4_2011DEC=7

Re: Transpose

art297 — Mon, 14 Jan 2013 15:57:47 GMT

: 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;

Re: Transpose

SASPhile — Mon, 14 Jan 2013 18:38:16 GMT

Hi Arthur,

I used your data step for 50 Mil records and it completed way faster than expected. I could not keep the old Log as I had overwriiten with other process.

Re: Transpose

data_null__ — Mon, 14 Jan 2013 19:06:09 GMT

Have you tested when you have lots more dates? Is there a limit to the number of data sets that can be merged.

Or course you could probabley use multiple data steps and still be faster than proc transpose.

Re: Transpose

art297 — Mon, 14 Jan 2013 19:47:50 GMT

dn: what would you suggest as "a lot more dates", specifically, how large would you suggest?

The OP for this thread only needed 12 dates, so I didn't push it. Interestingly, using proc transpose on just the 4 replications of the 12 dates on 12 million records almost blew out my ram.

Re: Transpose

data_null__ — Mon, 14 Jan 2013 20:11:35 GMT

I don't know how many. I was thinking that for the WHEREed MERGE technique that the number of MERGE data sets is the limiting factor (or at least I think so) and I would like to know where that limit is.

Re: Transpose

art297 — Mon, 14 Jan 2013 20:28:28 GMT

: I'm currently testing out DN's suggestion of testing some of the limits, but quickly confronted the log situation. That is easy to circumvent by just rerouting the log. I wrapped the code with:

proc printto log="c:\art\testlog";

run;

/* the code */

proc printto;

run;

Re: Transpose

art297 — Mon, 14 Jan 2013 22:23:46 GMT

DN: I've tested it, both with using call execute and with creating and running an include program.

I successfully ran 256, 512 and 1024 merges, but the system crashed as 2048. But, even with 1024, that was creating a file that was 4,097 variables wide. I wouldn't recommend anyone creating that wide of a file anyway.

The limitations, also, may just be due to limited ram. Can you try the following on your computer and, if it works, increasing it to 4096?:

data have (drop=months);

format id best32.;

input date ind1-ind4 ;

informat date date9.;

format date yymon7.;

do id=1 to 2;

date="01dec2010"d;

do months=1 to 2048;

date=intnx('month',date,1);

output;

end;

cards;

01dec2010 1 2 3 4

;

proc printto log="c:\testlog";

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;

proc printto;

run;

Re: Transpose

SASPhile — Tue, 15 Jan 2013 18:42:59 GMT

Art,

It took over 3 hrs with 50 mil records.

Re: Transpose

art297 — Tue, 15 Jan 2013 19:33:49 GMT

: Then I would guess that your input file has quite a few extra variables in it or that there is something else going on. I tested the process on 50 million records, on an extremely old computer with extremely limited ram, and it only took 7 minutes.

Re: Transpose

Astounding — Tue, 15 Jan 2013 19:59:43 GMT

Art,

I like the general idea, but my gut says you would get faster results by switching from MERGE to SET. You would have to go through the usual complications ... what to do if first.id, what to retain, what to do if last.id. I'm up to my eyeballs for the next few weeks and won't be able to test (sorry).

Good luck.

Re: Transpose

SASPhile — Tue, 15 Jan 2013 21:37:29 GMT

Yes there are extra variables.

Re: Transpose

art297 — Tue, 15 Jan 2013 21:51:38 GMT

: Are they the same for all of a subject's 12 records? If not, you will have a problem with any method as only the last record's info will be kept.

If the values for those variables are the same for all 12 records, you can speed up the process by (after sorting the file) extracting the data to be transposed (i.e., id, date and the four ind variables) to one file and, for the last.id, outputting just id and the variables that aren't part of the transpose, running the transpose on the first of those files, and then merging the results with the second one.

BTW, the 7 minute proc execute solution (which took the same cpu and real time) took 40.4 minutes cpu time and over an hour real time using the double transpose method.

Re: Transpose

art297 — Tue, 15 Jan 2013 23:04:51 GMT

: Your gut was telling you the truth. It reduced the run on the 50 million test case from 7 minutes to 2 minutes.

: I haven't had time to turn this into a general solution yet, but here is the code (based on Asounding's sugestion), that accomplished the test run in 2 minutes. It will still require sorted data, but doesn't require the upfront sql code as I hard coded the variables, and should be able to accomodate your full length records.

data want;

array want(48)ind1_2011JAN ind2_2011JAN ind3_2011JAN ind4_2011JAN

ind1_2011FEB ind2_2011FEB ind3_2011FEB ind4_2011FEB

ind1_2011MAR ind2_2011MAR ind3_2011MAR ind4_2011MAR

ind1_2011APR ind2_2011APR ind3_2011APR ind4_2011APR

ind1_2011MAY ind2_2011MAY ind3_2011MAY ind4_2011MAY

ind1_2011JUN ind2_2011JUN ind3_2011JUN ind4_2011JUN

ind1_2011JUL ind2_2011JUL ind3_2011JUL ind4_2011JUL

ind1_2011AUG ind2_2011AUG ind3_2011AUG ind4_2011AUG

ind1_2011SEP ind2_2011SEP ind3_2011SEP ind4_2011SEP

ind1_2011OCT ind2_2011OCT ind3_2011OCT ind4_2011OCT

ind1_2011NOV ind2_2011NOV ind3_2011NOV ind4_2011NOV

ind1_2011DEC ind2_2011DEC ind3_2011DEC ind4_2011DEC;

retain want;

set have;

by id;

if first.id then call missing(of want(*));

want((month(date)-1)*4+1)=ind1;

want((month(date)-1)*4+2)=ind2;

want((month(date)-1)*4+3)=ind3;

want((month(date)-1)*4+4)=ind4;

drop date ind1-ind4;

if last.id then output;

run;

Re: Transpose

data_null__ — Tue, 15 Jan 2013 23:36:53 GMT

You might consider this technique to create the array.

proc summary nway data=have(keep=date);
   class date;
   output out=udate(drop=_type_ _Freq_);
   run; 
data udate;
   set udate;
   do i = 1 to 4; 
      _name_ = cats('ind',i);
      output; 
      end; 
   run; 
proc sort; 
   by i date;
   run; 
proc transpose data=udate delimiter=_ out=frame(drop=_name_);
   var i;
   id _name_ date;
   run;

Then use data FRAME in your data step with an unexecuted SET. Saves typing the array statement.

   if 0 then set frame;
   array want 
_numeric_;

This is the first two lines of data want.

Re: Transpose

art297 — Wed, 16 Jan 2013 02:05:25 GMT

DN: Most definitely! I was surprised to discover that the upfront proc summary call ran in around 30% of the time it took the initial proc sql call to run. While this was an SGF quality paper already with just Ksharp's idea, combined with your, Asounding's and my input, it will definitely be a nice addition to the SAS-related literature.

Re: Transpose

data_null__ — Wed, 16 Jan 2013 15:15:36 GMT

I wonder if there is much or any performance advance by factoring out the MONTH function

if first.id then call missing(of want(*));

m = (month(date)-1)*4;

want(m+1)=ind1;

want(m+2)=ind2;

want(m+3)=ind3;

want(m+4)=ind4;

drop date m ind1-ind4;

if last.id then output;