topic Re: Transpose data in SAS Programming

Transpose data

Ganeshk — Wed, 11 May 2016 13:26:26 GMT

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

Re: Transpose data

DartRodrigo — Wed, 11 May 2016 13:58:47 GMT

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.

Re: Transpose data

RW9 — Wed, 11 May 2016 14:05:00 GMT

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;

Re: Transpose data

Rushikesh — Wed, 11 May 2016 14:37:03 GMT

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;

Re: Transpose data

RW9 — Wed, 11 May 2016 14:44:48 GMT

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?

Re: Transpose data

Rushikesh — Wed, 11 May 2016 15:05:10 GMT

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

Re: Transpose data

Reeza — Wed, 11 May 2016 15:15:40 GMT

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.

Re: Transpose data

stat_sas — Wed, 11 May 2016 15:33:57 GMT

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;

Re: Transpose data

ramchinna24 — Thu, 12 May 2016 02:07:05 GMT

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

Re: Transpose data

Ksharp — Thu, 12 May 2016 03:05:15 GMT

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;

Re: Transpose data

ramchinna24 — Thu, 12 May 2016 03:34:16 GMT

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;

Re: Transpose data

Reeza — Thu, 12 May 2016 03:56:30 GMT

This code is far from optimal, but I think you'll understand it and should be able to modify and extend it if necessary.

It would help if you posted data that was closer to your actual problem, or state the limitations up front.

It's based on @RW9 solution above, but extended to account for unknown number of variables per variable type.

Untested, since there's no sample data and I'm too lazy to make any.

data want;
  set have;
  array vol_{*}; *Use asterisk to indicate unknown dimension, uses all variables starting with vol_;
  array rev_{*};
  array profit_{*};
  array sp_{*};

max_counter = max(dim(vol_), dim(rev_), dim(profit_), dim(sp_));

  do i=1 to max_counter;
   call missing(volume, revenue, profit, spend); *Set to missing at top of array;
   *Assign if value exists, otherwise it stays missing; 
   if i <= dim(vol_) then volume=vol_(i);
    if i <= dim(rev_) then revenue=rev_(i);
    if i <= dim(profit_) then profit=prof_(i);
    if i <= dim(sp_) then spend=sp_(i);
    output;
  end;


run;

Re: Transpose data

ramchinna24 — Thu, 12 May 2016 04:17:18 GMT

I gave sample data please check. still i am getting same reults: please verify it. dono how to put missing for other observations

data have;
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 sp_5;
cards;
1 ABC cv 2 3 4 10 34 55 12 12 11 20 21 22 23 33
2 xyz xc 4 6 7 23 44 66 14 15 12 23 23 25 26 12
;
run;

Re: Transpose data

Reeza — Thu, 12 May 2016 05:36:22 GMT

@ramchinna24 wrote:

still i am getting same reults: please verify it. dono how to put missing for other observations

What does that mean? I don't know what same results means. The code below works for me, I'm leaving the rest up to you.

data have;
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 sp_5;
cards;
1 ABC cv 2 3 4 10 34 55 12 12 11 20 21 22 23 33
2 xyz xc 4 6 7 23 44 66 14 15 12 23 23 25 26 12
;
run;

data want;
  set have;
  array vol_{*} vol_:; *Use asterisk to indicate unknown dimension, uses all variables starting with vol_;
  array rev_{*} rev_:;
  array profit_{*} profit_:;
  array sp_{*} sp_:;

max_counter = max(dim(vol_), dim(rev_), dim(profit_), dim(sp_));

  do i=1 to max_counter;
   call missing(volume, revenue, profit, spend); *Set to missing at top of array;
   *Assign if value exists, otherwise it stays missing; 
   if i <= dim(vol_) then volume=vol_(i);
    if i <= dim(rev_) then revenue=rev_(i);
    if i <= dim(profit_) then profit=profit_(i);
    if i <= dim(sp_) then spend=sp_(i);
    
    If I>1 then call missing(category, subcategory);

    Output;
  end;

drop vol_: rev_: profit_: sp_:;

run;

Re: Transpose data

Ganeshk — Thu, 12 May 2016 05:25:34 GMT

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

Re: Transpose data

Ksharp — Thu, 12 May 2016 05:35:15 GMT

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;

Re: Transpose data

ramchinna24 — Thu, 12 May 2016 05:38:23 GMT

Perfect.. Thank you Xia

Re: Transpose data

Ksharp — Thu, 12 May 2016 05:38:52 GMT

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;