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 |
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 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;
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.
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.
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.
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.