topic Re: Transpose Wide to Long while duplicating qualitative data in SAS Procedures

Transpose Wide to Long while duplicating qualitative data

GlenGold — Thu, 19 May 2011 14:49:35 GMT

I have a data set with weekly sales and units for the year in each row. There are also many qualitative variables such as product form, type, size, etc. I need the data transposed so that the sales and units for each week are on a separate row, and the qualitative variables are duplicated for each entry.

Example:

Product UPC Form Type Week1 Week2 Week3 Week4 Unit1 Unit2 Unit3 Unit4

I need:

Product UPC Form Type Week1 Unit1
Product UPC Form Type Week2 Unit2
Product UPC Form Type Week3 Unit3
Product UPC Form Type Week4 Unit4

I created a macro in excel and it was working but I ran out of rows! This set will be 1.5 million rows when done, excel has a max ~1 mill. I found some information on a macro in SAS which does this but couldn't get it to install. I've never used macros in sas

Re: Transpose Wide to Long while duplicating qualitative data

GlenGold — Thu, 19 May 2011 16:27:14 GMT

Well I did the transposing in Enterprise Guide and am left with two sets, one with sales, one with units. I cannot find a way to merge them together.

Re: Transpose Wide to Long while duplicating qualitative data

scmebu — Thu, 19 May 2011 16:33:32 GMT

Something like this???



ods html close;

ods listing;



data foo;

 input Product UPC Form Type Week1 Week2 Week3 Week4 Unit1 Unit2 Unit3 Unit4;

 cards;

 01 01 01 01 05 06 07 08 09 10 11 12

 02 02 02 02 21 22 23 24 25 26 27 28

 01 01 01 01 13 14 15 16 17 18 19 20

 02 02 02 02 29 30 31 32 33 34 35 36

 ;

run;



proc sort data=foo;

 by Product UPC Form Type;

run;



data bar;

 set foo;

 by Product UPC Form Type;

 array W[4] Week1-Week4;

 array U[4] Unit1-Unit4;

 do i=1 to 4;

  Week=W;

  Unit=U;

  index=i;

  output;

 end;



 drop i;

 drop Week1 Week2 Week3 Week4;

 drop Unit1 Unit2 Unit3 Unit4;

run;



proc print data=bar;

run;

Message was edited by: scmebu

Re: Transpose Wide to Long while duplicating qualitative data

scmebu — Thu, 19 May 2011 16:37:28 GMT

Sorry, I'm having problems preserving the text of the code when it's posted.

Re: Transpose Wide to Long while duplicating qualitative data

scmebu — Thu, 19 May 2011 16:39:47 GMT

ods html close;
ods listing;

data foo;
input Product UPC Form Type Week1 Week2 Week3 Week4 Unit1 Unit2 Unit3 Unit4;
cards;
01 01 01 01 05 06 07 08 09 10 11 12
02 02 02 02 21 22 23 24 25 26 27 28
01 01 01 01 13 14 15 16 17 18 19 20
02 02 02 02 29 30 31 32 33 34 35 36
;
run;

proc sort data=foo;
by Product UPC Form Type;
run;

data bar;
set foo;
by Product UPC Form Type;
array W[4] Week1-Week4;
array U[4] Unit1-Unit4;
do i=1 to 4;
Week=W [ i ];
Unit=U [ i ];
index=i;
output;
end;

drop i;
drop Week1 Week2 Week3 Week4;
drop Unit1 Unit2 Unit3 Unit4;
run;

proc print data=bar;
run;

Re: Transpose Wide to Long while duplicating qualitative data

SPR — Thu, 19 May 2011 20:19:33 GMT

Hello GlenGold,

This is a transpose based solution:
[pre]
data i;
input Product UPC Form Type Week1 Week2 Week3 Week4 Unit1 Unit2 Unit3 Unit4;
cards;
01 01 01 01 05 06 07 08 09 10 11 12
02 02 02 03 21 22 23 24 25 26 27 28
01 01 01 02 13 14 15 16 17 18 19 20
02 02 02 04 29 30 31 32 33 34 35 36
;
run;
proc sort data=i;
by Product UPC Form Type;
run;
proc transpose data=i out=w (drop=_:) prefix=Amount;
var week1-week4;
by Product UPC Form Type;
run;
proc transpose data=i out=u (drop=_:) prefix=Sales;
var Unit1-Unit4;
by Product UPC Form Type;
run;
data r;
merge w u;
by Product UPC Form Type;
run;
[/pre]
Sincerely,
SPR