Transpose data in 5 variables

[MV04]

Hi All,

I would really appreciate if someone could help me with the following.

I need to transpose below data 

GRP_a	GRP_B	GRP_C	GRP_d	GRP_E	Total_Value	Total_Volume
abc	key	ColX		aa	GBP 39,775,288	1634267
abc	key	ColX		bb	GBP 142,123,608	722948
abc	key	ColX		cc	GBP 813,636,979	22434774

to this format 

GRP_a	GRP_B	GRP_C	GRP_d	aa_Value	aa_Vol	bb_Value	bb_Volume	cc_Value	cc_Volume
abc	key	ColX		GBP 39,775,288	1634267	###########	722948	########	22434774

[Ksharp · Accepted solution]

If you don't have a big table try double proc transpose.

data have;
infile cards expandtabs truncover;
input (GRP_a	GRP_B	GRP_C	GRP_d	GRP_E	Total_Value	Total_Volume ) (:$20.);
cards;
abc	key	ColX	. 	aa	GBP39,775,288	1634267
abc	key	ColX	. 	bb	GBP142,123,608	722948
abc	key	ColX	. 	cc	GBP813,636,979	22434774
;

proc transpose data=have out=temp;
by GRP_a	GRP_B	GRP_C	GRP_d GRP_E;
var Total_Value	Total_Volume;
run;
proc transpose data=temp out=want delimiter=_ ;
by GRP_a	GRP_B	GRP_C	GRP_d ;
var col1;
id GRP_E _name_;
run;