BookmarkSubscribeRSS Feed
Syndey
Calcite | Level 5

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
4 REPLIES 4
ballardw
Super User

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

art297
Opal | Level 21

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;

Syndey
Calcite | Level 5

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.

art297
Opal | Level 21

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 855 views
  • 0 likes
  • 3 in conversation