How to replace value with variables name

[ursula]

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.

[Reeza]

Do you only have 1/2's in your data set?

[Reeza]

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;

[ursula]

it's a good question.

actually some others are 0 or 3, but I only need values that =2.

[ballardw · Accepted solution]

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.

[art297]

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