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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.