Transpose Wide to Long while duplicating qualitative data

[GlenGold]

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

[GlenGold]

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.

[scmebu]

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

[scmebu]

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

[scmebu]

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;

[SPR]

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