DATA Step, Macro, Functions and more

How to replace value with variables name

Accepted Solution Solved
Reply
Highlighted
Contributor
Posts: 61
Accepted Solution

How to replace value with variables name

[ Edited ]

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.


Accepted Solutions
Solution
‎03-20-2017 05:03 PM
Grand Advisor
Posts: 9,722

Re: How to replace value with variables name

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.

 

View solution in original post


All Replies
Grand Advisor
Posts: 16,328

Re: How to replace value with variables name

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

Grand Advisor
Posts: 16,328

Re: How to replace value with variables name

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;
Contributor
Posts: 61

Re: How to replace value with variables name

it's a good question.

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

Solution
‎03-20-2017 05:03 PM
Grand Advisor
Posts: 9,722

Re: How to replace value with variables name

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.

 

Esteemed Advisor
Posts: 6,885

Re: How to replace value with variables name

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

 

Post a Question
Discussion Stats
  • 5 replies
  • 102 views
  • 3 likes
  • 4 in conversation