BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ursula
Pyrite | Level 9

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.

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

5 REPLIES 5
Reeza
Super User

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

Reeza
Super User

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
Pyrite | Level 9

it's a good question.

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

ballardw
Super User

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
Opal | Level 21

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

 

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1748 views
  • 3 likes
  • 4 in conversation