DATA Step, Macro, Functions and more

Transpose data

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

Transpose data

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


Accepted Solutions
Solution
‎05-12-2016 01:18 AM
Super User
Posts: 9,676

Re: Transpose data

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;

View solution in original post


All Replies
Regular Contributor
Posts: 212

Re: Transpose data

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.

Super User
Super User
Posts: 7,401

Re: Transpose data

[ Edited ]

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;
New Contributor
Posts: 3

Re: Transpose data

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;

 

 

 

Super User
Super User
Posts: 7,401

Re: Transpose data

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?

New Contributor
Posts: 3

Re: Transpose data

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

 

 

Contributor
Posts: 27

Re: Transpose data

For same problem: Suppose if there is extra column Sp_4 with some values then how to write program? please help.

Super User
Posts: 17,819

Re: Transpose data

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. 

Trusted Advisor
Posts: 1,204

Re: Transpose data

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;

Solution
‎05-12-2016 01:18 AM
Super User
Posts: 9,676

Re: Transpose data

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;
Contributor
Posts: 27

Re: Transpose data

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;

Super User
Posts: 9,676

Re: Transpose data

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;
Contributor
Posts: 27

Re: Transpose data

Perfect.. Thank you Xia
Contributor
Posts: 29

Re: Transpose data

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

 

Super User
Posts: 9,676

Re: Transpose data

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;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 17 replies
  • 441 views
  • 7 likes
  • 8 in conversation