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