Proc SQL: Combine Multiple Row Values into One Column/Row

[RRRR5]

Hi All,

This should be pretty simple but I'm having difficulty figuring it out.

I have the following input :

ID	TYPE	AMOUNT	COUNT
111	TYPE1	100000	4
111	TYPE2	403000	7
111	TYPE3	230000	3
222	TYPE1	40000	2
222	TYPE2	90600	1
222	TYPE3	20000	2

and required output in following format.

ID	TYPE1_TRAN_AMOUNT	TYPE1_TRAN_COUNT	TYPE2_TRAN_AMOUNT	TYPE2_TRAN_COUNT	TYPE3_TRAN_AMOUNT	TYPE3_TRAN_COUNT
111	100000	4	403000	7	230000	3
222	40000	2	90600	1	20000	2

Any help would be appreciated!!!

Thanks!

[Kurt_Bremser · Accepted solution]

Start with

proc transpose
  data=have
  out=want
;
by id;
id type;
var amount count;
run;

and see how far that gets you.

[Kurt_Bremser]

Note that a wide dataset will be a PITA in further processing. See Maxim 49.