BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ganeshk
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

17 REPLIES 17
DartRodrigo
Lapis Lazuli | Level 10

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
Rushikesh
Calcite | Level 5

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;

 

 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

Rushikesh
Calcite | Level 5

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

 

 

ramchinna24
Obsidian | Level 7

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

Reeza
Super User

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. 

stat_sas
Ammonite | Level 13

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;

Ksharp
Super User

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;
ramchinna24
Obsidian | Level 7

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;

Ksharp
Super User

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;
ramchinna24
Obsidian | Level 7
Perfect.. Thank you Xia
Ganeshk
Obsidian | Level 7

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

 

Ksharp
Super User

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;

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!

How to Concatenate Values

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.

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
  • 17 replies
  • 1590 views
  • 7 likes
  • 8 in conversation