Help using Base SAS procedures

Proc transpose or not

Reply
Occasional Contributor
Posts: 5

Proc transpose or not

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

IDFlag_1Flag_2Flag_3Flag_4Flag_5Flag_6Flag_7Flag_8Flag_9Flag_10Flag_11Flag_12
A1347
B12346
C123456
D2456
E36

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

IDCOMBINATION
A1
A3
A4
A7
B1
B2
B3
B4
B6
C1
C2
C3
C4
C5
C6
D2
D4
D5
D6
E3
E6
Super User
Posts: 11,343

Re: Proc transpose or not

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

PROC Star
Posts: 7,468

Re: Proc transpose or not

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;

Occasional Contributor
Posts: 5

Re: Proc transpose or not

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.

PROC Star
Posts: 7,468

Re: Proc transpose or not

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.

Ask a Question
Discussion stats
  • 4 replies
  • 258 views
  • 0 likes
  • 3 in conversation