Hi,
My data is:
Item | Category | Subcategory | Vol_1 | Vol_2 | Vol_3 | Rev_1 | Rev_2 | Rev_3 | Profit_1 | Profit_2 | Profit_3 | SP_1 | SP_2 | SP_3 |
1 | ABC | cv | 2 | 3 | 4 | 10 | 34 | 55 | 12 | 12 | 11 | 20 | 21 | 22 |
2 | xyz | xc | 4 | 6 | 7 | 23 | 44 | 66 | 14 | 15 | 12 | 23 | 23 | 25 |
I need to transpose like:
Item | Category | Subcategory | Volume | Volume_Fig | Revenue | Revenue_Fig | Profit | Profit_Fig | SP | Sp_Fig |
1 | ABC | cv | Vol_1 | 2 | Rev_1 | 10 | Profit_1 | 12 | SP_1 | 20 |
Vol_2 | 3 | Rev_2 | 34 | Profit_2 | 12 | SP_2 | 21 | |||
Vol_3 | 4 | Rev_3 | 55 | Profit_3 | 11 | SP_3 | 22 |
Thanks,
Ganesh K
Use proc transpose firstly ,then use MERGE Skill me ,Arthur.T, Matt proposed:
http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf
data have;
infile cards truncover expandtabs ;
input Item Category $ Subcategory $
Vol_1 Vol_2 Vol_3
Rev_1 Rev_2 Rev_3
Profit_1 Profit_2 Profit_3
SP_1 SP_2 SP_3;
cards;
1 ABC cv 2 3 4 10 34 55 12 12 11 20 21 22
2 xyz xc 4 6 7 23 44 66 14 15 12 23 23 25
;
run;
proc transpose data=have out=temp;
by Item Category Subcategory;
run;
data temp;
set temp;
key=upcase(scan(_name_,1,'_'));
run;
proc sql;
select distinct catt('temp(where=(key="',key,'")
rename=(_name_=',key,' col1=',key,'_Fig))')
into : list separated by ' '
from temp;
quit;
data want;
merge &list ;
by Item Category Subcategory;
drop key;
run;
Hi mate.
Maybe this can help you, check Overview: TRANSPOSE Procedure or SOME USES (AND HANDY ABUSES) OF PROC TRANSPOSE , here you may find the type of transpose you need.
Hope this helps.
Hi,
Its a good idea to post test data in the form of a datastep, as such this code is not tested:
data want; set have; array vol_{3}; array rev_{3}; array profit_{3}; array sp_{3}; do i=1 to 3; volume=cats("Vol_",put(i,1.)); volume_fig=vol_{i}; revenue=cats("Rev_",put(i,1.)); revenue_fig=rev_{i}; profit=cats("Profit_",put(i,1.)); profit_fig=profit_{i}; sp=cats("SP_",put(i,1.)); sp_fig=sp_{i}; output; end; run;
Check this if its useful. Its lenghty but final dataset has the output what you expected...
data test;
Input Item Category $ Subcategory $ Vol_1 Vol_2 Vol_3 Rev_1 Rev_2 Rev_3 Profit_1 Profit_2 Profit_3 SP_1 SP_2 SP_3;
cards;
1 ABC cv 2 3 4 10 34 55 12 12 11 20 21 22
2 xyz xc 4 6 7 23 44 66 14 15 12 23 23 25
;
run;
data Vol(keep= Item Category Subcategory Vol_1 Vol_2 Vol_3);
set test;
run;
data Rev(keep= Item Category Subcategory Rev_1 Rev_2 Rev_3);
set test;
run;
data Profit(keep= Item Category Subcategory Profit_1 Profit_2 Profit_3);
set test;
run;
data SP(keep= Item Category Subcategory SP_1 SP_2 SP_3);
set test;
run;
proc transpose data=Vol
out=Vol_tp(rename=(col1=Volume_Fig))
name=Volume;
by Item category Subcategory ;
label volume=" ";
run;
proc transpose data=Rev
out=Rev_tp(rename=(col1=Revenue_Fig))
name=Revenue;
by Item category Subcategory ;
label Revenue=" ";
run;
proc transpose data=Profit
out=Profit_tp(rename=(col1=Profit_Fig))
name=Profit;
by Item category Subcategory ;
label Profit=" ";
run;
proc transpose data=SP
out=SP_tp(rename=(col1=SP_Fig))
name=SP;
by Item category Subcategory ;
label SP=" ";
run;
data final;
merge vol_tp rev_tp profit_tp sp_tp;
by item category subcategory;
run;
A tip on that code, if you change the four initial datasteps then you avoid the need to tranpose each one:
data d1 (keep=item category subcategory vol_1 rev_1 profit_1 sp_1 rename=(vol_1=vol rev_1=rev profit_1=profit sp_1=sp)); set test; run data d2 (keep=item category subcategory vol_2 rev_2 profit_2 sp_2 rename=(vol_2=vol rev_2=rev profit_2=profit sp_2=sp)); set test; run; data d3 (keep=item category subcategory vol_3 rev_3 profit_3 sp_3 rename=(vol_3=vol rev_3=rev profit_3=profit sp_3=sp)); set test; run; data want; set d1 d2 d3; run;
However, why do all those steps when you can do it one?
Hi RW9,
I tried executing your code. The final structure of the output table is perfect as per the requirement but the values are dragged over observation. Also there are some unnecessary columns in final dataset. See below:
data want;
1130 set test;
1131 array vol_{3};
1132 array rev_{3};
1133 array profit_{3};
1134 array sp_{3};
1135 do i=1 to 3;
1136 volume=cats("Vol_",put(i,1.));
1137 volume_fig=vol_{i};
1138 revenue=cats("Rev_",put(i,1.));
1139 revenue_fig=rev_{i};
1140 profit=cats("Profit_",put(i,1.));
1141 profit_fig=profit_{i};
1142 sp=cats("SP_",put(i,1.));
1143 sp_fig=sp_{i};
1144 output;
1145 end;
1146 run;
NOTE: There were 2 observations read from the data set WORK.TEST.
NOTE: The data set WORK.WANT has 6 observations and 24 variables.
NOTE: Compressing data set WORK.WANT increased size by 100.00 percent.
Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: DATA statement used (Total process time):
real time 0.39 seconds
cpu time 0.01 seconds
But with the code i shared, we get the final table structure as well as desired values and columns perfectly. Still Your code and use of arrays is really impressive. It reduced a lot of typing work. Thanks for sharing that technique.
-Rishi
For same problem: Suppose if there is extra column Sp_4 with some values then how to write program? please help.
Do the variables, Volume, Revenue, Profit that hold the values Volume_1, Volume_2, Volume_3, Revenue_1 etc add any value?
If this is actually how your data looks, I would suggest dropping those variables and adding a single record counter that held 1/2/3 instead.
Just a thought.
data want(drop=i);
set have;
array a Vol_1 Vol_2 Vol_3 Rev_1 Rev_2 Rev_3 Profit_1 Profit_2 Profit_3 SP_1 SP_2 SP_3;
do i=1 to 3;
volume=vname(a(i));
volume_fig=a(i);
Revenue=vname(a(i+3));
Revenue_fig=a(i+3);
Profit=vname(a(i+6));
Profit_fig=a(i+6);
SP=vname(a(i+9));
SP_fig=a(i+9);
output;
end;
drop Vol_1 Vol_2 Vol_3 Rev_1 Rev_2 Rev_3 Profit_1 Profit_2 Profit_3 SP_1 SP_2 SP_3;
run;
Use proc transpose firstly ,then use MERGE Skill me ,Arthur.T, Matt proposed:
http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf
data have;
infile cards truncover expandtabs ;
input Item Category $ Subcategory $
Vol_1 Vol_2 Vol_3
Rev_1 Rev_2 Rev_3
Profit_1 Profit_2 Profit_3
SP_1 SP_2 SP_3;
cards;
1 ABC cv 2 3 4 10 34 55 12 12 11 20 21 22
2 xyz xc 4 6 7 23 44 66 14 15 12 23 23 25
;
run;
proc transpose data=have out=temp;
by Item Category Subcategory;
run;
data temp;
set temp;
key=upcase(scan(_name_,1,'_'));
run;
proc sql;
select distinct catt('temp(where=(key="',key,'")
rename=(_name_=',key,' col1=',key,'_Fig))')
into : list separated by ' '
from temp;
quit;
data want;
merge &list ;
by Item Category Subcategory;
drop key;
run;
It is almost right but Vol_1 vol2 vol_3 and other rev_ and profit_ variables has three types only but sp_ has 4 variables
what i mean is vol_3,rev_3 are repeating in 4th observation when we have not equal type of variables.so to stop repeating and place "." in repeating places . because we may have different types of variables(for eg: vol_1-vol6,7, and so on). so can you please tell me how to achieve it?
your previous code:
with sp_4 extra column:
data test;
input Item Category $ Subcategory $ Vol_1 Vol_2 Vol_3 Rev_1 Rev_2 Rev_3 Profit_1 Profit_2 Profit_3 SP_1 SP_2 SP_3 sp_4;
cards;
1 ABC cv 2 3 4 10 34 55 12 12 11 20 21 22 23
2 xyz xc 4 6 7 23 44 66 14 15 12 23 23 25 26
;
run;
proc transpose data=have out=temp;
by Item Category Subcategory;
run;
data temp;
set temp;
key=upcase(scan(_name_,1,'_'));
run;
proc sql;
select distinct catt('temp(where=(key="',key,'")
rename=(_name_=',key,' col1=',key,'_Fig))')
into : list separated by ' '
from temp;
quit;
data want;
merge &list ;
by Item Category Subcategory;
drop key;
run;
OK. Here is. I just add two more statements at the end of code.
data test;
input Item Category $ Subcategory $ Vol_1 Vol_2 Vol_3 Rev_1 Rev_2 Rev_3 Profit_1 Profit_2 Profit_3 SP_1 SP_2 SP_3 sp_4;
cards;
1 ABC cv 2 3 4 10 34 55 12 12 11 20 21 22 23
2 xyz xc 4 6 7 23 44 66 14 15 12 23 23 25 26
;
run;
proc transpose data=test out=temp;
by Item Category Subcategory;
run;
data temp;
set temp;
key=upcase(scan(_name_,1,'_'));
run;
proc sql;
select distinct catt('temp(where=(key="',key,'")
rename=(_name_=',key,' col1=',key,'_Fig))')
into : list separated by ' '
from temp;
quit;
data want;
merge &list ;
by Item Category Subcategory;
output;
call missing(of _all_);
drop key;
run;
Hi Ksharp
I need one more solution for same program:
Item | Category | Subcategory | Volume | Volume_Fig | Revenue | Revenue_Fig | Profit | Profit_Fig | SP | Sp_Fig |
1 | ABC | cv | Vol_1 | 2 | Rev_1 | 10 | Profit_1 | 12 | SP_1 | 20 |
0 | Vol_2 | 3 | Rev_2 | 34 | Profit_2 | 12 | SP_2 | 21 | ||
0 | Vol_3 | 4 | Rev_3 | 55 | Profit_3 | 11 | SP_3 | 22 |
Here only for first observation should only contain values. Rest of all obs. should have "0" (numeric) or " "(character)
Thanks,
Ganesh K
It sounds like you are making a report. If it was, try proc report is able to achieve that goal.
Otherwise, Here is :
data test;
input Item Category $ Subcategory $ Vol_1 Vol_2 Vol_3 Rev_1 Rev_2 Rev_3 Profit_1 Profit_2 Profit_3 SP_1 SP_2 SP_3 sp_4;
cards;
1 ABC cv 2 3 4 10 34 55 12 12 11 20 21 22 23
2 xyz xc 4 6 7 23 44 66 14 15 12 23 23 25 26
;
run;
proc transpose data=test out=temp;
by Item Category Subcategory;
run;
data temp;
set temp;
key=upcase(scan(_name_,1,'_'));
run;
proc sql;
select distinct catt('temp(where=(key="',key,'")
rename=(_name_=',key,' col1=',key,'_Fig))')
into : list separated by ' '
from temp;
quit;
data want;
merge &list ;
by Item Category Subcategory;
output;
call missing(of _all_);
drop key;
run;
data want;
set want;
by Item Category Subcategory;
if not first.Subcategory then call missing(Item,Category,Subcategory);
run;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.