topic Proc transpose or not in SAS Procedures

Proc transpose or not

Syndey — Thu, 02 Apr 2015 22:34:14 GMT

Hi I have the below data for 400K customers (1 row per customer with a different combination for each customer).

ID	Flag_1	Flag_2	Flag_3	Flag_4	Flag_5	Flag_6	Flag_7	Flag_8	Flag_9	Flag_10	Flag_11	Flag_12
A	1			3		4			7
B	1		2			3		4		6
C				1	2	3		4	5			6
D		2					4			5	6
E				3					6

I want to end up with two columns like below. Any ideas the best way to do this. thanks

ID	COMBINATION
A	1
A	3
A	4
A	7
B	1
B	2
B	3
B	4
B	6
C	1
C	2
C	3
C	4
C	5
C	6
D	2
D	4
D	5
D	6
E	3
E	6

Re: Proc transpose or not

ballardw — Thu, 02 Apr 2015 22:45:47 GMT

data want (keep=ID combination);

set have;

array f flag: ;

do j=1 to dim(f);

combination = f;

if not missing(combination) then output;

end;

run;

might do the trick

Re: Proc transpose or not

art297 — Thu, 02 Apr 2015 22:51:00 GMT

Proc transpose would work too:

proc transpose data=have out=want (drop=_:

rename=(col1=combination)

where=(not missing(combination)));

var flag_1-flag_12;

by id;

run;

Re: Proc transpose or not

Syndey — Fri, 03 Apr 2015 21:23:59 GMT

Hi Arthur

Thanks for the reply. I tried doing:

Proc transpose data=old out=new;

by id;

var flag_1 flag_2 flag_3 flag_4 flag_5 flag_6 flag_7 flag_8 flag_9 flag_10 flag_11 flag_12;

run;

but i ended up with 2 columns, so id col1 col2. I'm not sure why, Any ideas? Sorry I am new to this all.

Re: Proc transpose or not

art297 — Fri, 03 Apr 2015 21:36:50 GMT

Are you sure that the extra column wasn't one labeled '_name_' ? I dropped that one with the code I had suggested, as renamed col1 to be combination (like you did in your example).

The only things you had to change in the code I suggested were the dataset names, old rather than have, and new rather than want.