Programming the statistical procedures from SAS

Need help with proc transpose...

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 76
Accepted Solution

Need help with proc transpose...

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 -

    Nameproductunitvolume
A1CC60
A1PL2116,650
A1SL138,930
A2CC90
A2SL120,588
A3CC40
A4PL13523,137
A4SL290,423
A5CC20
A5PL4176,813
A5SL153,988

Output Data -

NameCC UnitPL UnitSL UnitPL VolSL Vol
A1621116,65038,930
A2901020,588
A340000
A40132523,13790,423
A5241176,81353,988

Regards, Anil


Accepted Solutions
Solution
‎02-19-2013 08:45 AM
Super User
Super User
Posts: 6,708

Re: Need help with proc transpose...

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;

View solution in original post


All Replies
Solution
‎02-19-2013 08:45 AM
Super User
Super User
Posts: 6,708

Re: Need help with proc transpose...

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;

Frequent Contributor
Posts: 76

Re: Need help with proc transpose...

Thanks a lot Tom.. This is what I want it exactly...

Rgds, Anil


Super Contributor
Posts: 276

Re: Need help with proc transpose...

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

Regular Contributor
Posts: 152

Re: Need help with proc transpose...

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.


🔒 This topic is solved and locked.

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

Discussion stats
  • 4 replies
  • 182 views
  • 4 likes
  • 4 in conversation