Hi there,
I wonder if some one can help me.
see below original chart:
ID | A | B | C | D | E |
001 | 1 | 2 | 1 | 3 | 2 |
002 | 2 | 1 | 2 | 1 | 1 |
003 | 1 | 2 | 1 | 2 | 1 |
004 | 1 | 2 | 0 | 1 | 1 |
005 | 1 | 1 | 2 | 1 | 2 |
006 | 2 | 1 | 1 | 1 | 2 |
I want to replace all the (=2) values to the variables name.
see what I want:
ID | A | B | C | D | E |
001 | 1 | B | 1 | 3 | E |
002 | A | 1 | C | 1 | 1 |
003 | 1 | B | 1 | D | 1 |
004 | 1 | B | 0 | 1 | 1 |
005 | 1 | 1 | C | 1 | E |
006 | A | 1 | 1 | 1 | E |
and then the final result will be:
ID | COMBINE | |
001 | B,E | |
002 | A,C | |
003 | B,D | |
004 | B | |
005 | C,E | |
006 | A,E |
Thank you in advance.
One way:
data have; input ID A B C D E ; array x A B C D E; length result $ 10; do i= 1 to dim(x); if x[i] = 2 then result= catx(',',result,vname(x[i])); end; drop i; datalines; 001 1 2 1 3 2 002 2 1 2 1 1 003 1 2 1 2 1 004 1 2 0 1 1 005 1 1 2 1 2 006 2 1 1 1 2 run;
I didn't replace anything just accumulated the desired values.
Do you only have 1/2's in your data set?
Use PROC TRANSPOSE to flip and filter your dataset and then a data step to collate the results.
data have;
input ID $ A $ B $C $ D $ E $;
cards;
001 1 2 1 3 2
002 2 1 2 1 1
003 1 2 1 2 1
004 1 2 0 1 1
005 1 1 2 1 2
006 2 1 1 1 2
;
run;
proc transpose data=have out=flipped (where=(col1='2')) ;
by id;
var a b c d e;
run;
data want;
set flipped;
by id;
retain string;
if first.id then string=_name_;
else string = catx(',', string, _name_);
if last.id then output;
run;
it's a good question.
actually some others are 0 or 3, but I only need values that =2.
One way:
data have; input ID A B C D E ; array x A B C D E; length result $ 10; do i= 1 to dim(x); if x[i] = 2 then result= catx(',',result,vname(x[i])); end; drop i; datalines; 001 1 2 1 3 2 002 2 1 2 1 1 003 1 2 1 2 1 004 1 2 0 1 1 005 1 1 2 1 2 006 2 1 1 1 2 run;
I didn't replace anything just accumulated the desired values.
or, if you need the intermediary array as well AND your variable names are really A thru E:
data want (drop=i); set have; length combine $10; array iarray(5) a--e; array oarray(5) $ ca cb cc cd ce; do i=1 to dim(iarray); if iarray(i) eq '2' then oarray(i)=byte(i+64); end; combine=catx(',',of oarray(*)); run;
Art, CEO, AnalystFinder.com
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.