Dear All,
I have below dataset and want to transpose as below output data and i'm just not able to bring the output as shown. I used below proc transpose statement but for each name two observations appear - one with unit and other with volume. Requesting experts to help in re-writing this query.
proc transpose data = final_data_summ
out=final_data_transpose;
by agent_code;
var unit volume;
id product;
run;
Input Data -
Name | product | unit | volume |
A1 | CC | 6 | 0 |
A1 | PL | 2 | 116,650 |
A1 | SL | 1 | 38,930 |
A2 | CC | 9 | 0 |
A2 | SL | 1 | 20,588 |
A3 | CC | 4 | 0 |
A4 | PL | 13 | 523,137 |
A4 | SL | 2 | 90,423 |
A5 | CC | 2 | 0 |
A5 | PL | 4 | 176,813 |
A5 | SL | 1 | 53,988 |
Output Data -
Name | CC Unit | PL Unit | SL Unit | PL Vol | SL Vol |
A1 | 6 | 2 | 1 | 116,650 | 38,930 |
A2 | 9 | 0 | 1 | 0 | 20,588 |
A3 | 4 | 0 | 0 | 0 | 0 |
A4 | 0 | 13 | 2 | 523,137 | 90,423 |
A5 | 2 | 4 | 1 | 176,813 | 53,988 |
Regards, Anil
Use two transforms.
proc transpose data=have out=middle ;
by name product ;
run;
proc transpose data=middle out=want delimiter=_;
by name;
id product _name_;
var col1;
run;
Use two transforms.
proc transpose data=have out=middle ;
by name product ;
run;
proc transpose data=middle out=want delimiter=_;
by name;
id product _name_;
var col1;
run;
Thanks a lot Tom.. This is what I want it exactly...
Rgds, Anil
Hi Anil..
Here is the other way to get output.
Data final_data_summ;
input Name$ product $ unit volume;
cards;
A1 CC 6 0
A1 PL 2 116650
A1 SL 1 38930
A2 CC 9 0
A2 SL 1 20588
A3 CC 4 0
A4 PL 13 523137
A4 SL 2 90423
A5 CC 2 0
A5 PL 4 176813
A5 SL 1 53988
;
run;
proc print;
run;
proc transpose data = final_data_summ
out=final_data_transpose;
by name;
var unit volume;
id product;
run;
Data unit val;
set final_data_transpose;
if _name_="unit" then output unit;
else output val;
run;
Data Final(Drop=_name_);
merge Unit(rename=(CC=CCUnit PL=PLUnit SL=SLUnit)) Val(Rename=(PL=PLVal SL=SlVal));
by name;
run;
Regards..
Sanjeev.K
Transpose twice, the first time using UNIT as the VAR and the word, UNIT, as the prefix, the second time using VOLUME as the VAR and the word, VOLUME, as the prefix. Then, merge the two transposed output data sets.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.
Find more tutorials on the SAS Users YouTube channel.