BookmarkSubscribeRSS Feed
GlenGold
Calcite | Level 5
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
5 REPLIES 5
GlenGold
Calcite | Level 5
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
SAS Employee
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
SAS Employee
Sorry, I'm having problems preserving the text of the code when it's posted.
scmebu
SAS Employee
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
Quartz | Level 8 SPR
Quartz | Level 8
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 887 views
  • 0 likes
  • 3 in conversation