Need help with proc transpose...

[aj34321]

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 -

Name	product	unit	volume
A1	CC	6	0
A1	PL	2	116,650
A1	SL	1	38,930
A2	CC	9	0
A2	SL	1	20,588
A3	CC	4	0
A4	PL	13	523,137
A4	SL	2	90,423
A5	CC	2	0
A5	PL	4	176,813
A5	SL	1	53,988

Output Data -

Name	CC Unit	PL Unit	SL Unit	PL Vol	SL Vol
A1	6	2	1	116,650	38,930
A2	9	0	1	0	20,588
A3	4	0	0	0	0
A4	0	13	2	523,137	90,423
A5	2	4	1	176,813	53,988

Regards, Anil

[Tom · Accepted solution]

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;

[aj34321]

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

Rgds, Anil

[kuridisanjeev]

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

[1zmm]

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.