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

I’m going to use a simple example to ask my question because I'm not sure how to articulate what I'm trying to do.  If I have small groups within a dataset such as the ID column below.  I want to check to see if any record within an ID group has the color red.  If it does, I want to make all the records in that ID group red.  So that all 3 records for ID 1 and 3 would change to red and ID 2 would be unchanged.  How would I do that in SAS? 

 

ID               Color

       1         Blue

       1         Green

       1         Red

 

       2         Blue

       2         Yellow

 

       3         Green

       3          Red

       3          Yellow

 

Thanks in advance for any suggestion.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
data have;
input ID colour $;
cards;
1         Blue
1         Green
1         Red
2         Blue
2         Yellow
3         Green
3         Red
3         Yellow
;;;;
run;

proc sort data=have;
by id colour;
run;


data red_list;
set have;
where colour="Red";
by ID;
if first.ID;
rename colour=master_colour;
run;

data want;
merge have red_list;
by ID;
final_colour = coalescec(master_colour, colour);
run;

@ballardw solution but a data step approach instead. These are probably the best approaches given your question. It's possible to do this in a single step using DoW if efficiency becomes an issue but that's a more complex programming approach.

View solution in original post

5 REPLIES 5
maguiremq
SAS Super FREQ

Is this what you're looking for?

data have;
input id color :$7.;
datalines;
1 Blue
1 Green
1 Red
2 Blue
2 Yellow
3 Green
3 Red
3 Yellow
;

data want;
	merge	have (in = a)
			have (in = b rename = (color = color_red) where = (color_red = "Red"));
	by 		id;
	
			if a;
run;

I don't overwrite the original variable, but you could do that if you wanted. I just don't like doing that personally.

Obs id color color_red 
1 1 Blue Red 
2 1 Green Red 
3 1 Red Red 
4 2 Blue   
5 2 Yellow   
6 3 Green Red 
7 3 Red Red 
8 3 Yellow Red 
ballardw
Super User

@BillSut wrote:

I’m going to use a simple example to ask my question because I'm not sure how to articulate what I'm trying to do.  If I have small groups within a dataset such as the ID column below.  I want to check to see if any record within an ID group has the color red.  If it does, I want to make all the records in that ID group red.  So that all 3 records for ID 1 and 3 would change to red and ID 2 would be unchanged.  How would I do that in SAS? 

 

ID               Color

       1         Blue

       1         Green

       1         Red

 

       2         Blue

       2         Yellow

 

       3         Green

       3          Red

       3          Yellow

 

Thanks in advance for any suggestion.


One way, note the data step to provide an actual data set that can be used with code.

data have;
 input ID $ Color $;
datalines;
 1 Blue
 1 Green
 1 Red
 2 Blue
 2 Yellow
 3 Green
 3 Red
 3 Yellow
 ;


 Proc sql;
    create table want as
    select a.id, coalescec(b.color,a.color) as color
    from have as a
         left join
         (select distinct id,color from have
          where color='Red') as b
          on a.id=b.id
   ;
quit;

The Sql part selects the Id's that have Red somewhere in the  color variable using the (select distinct). The Join then matches those id to the basic data. The Coalescec function then returns the first value encountered in the two subsets. In the case where the ID did not have "red" then the B.color is missing and the A.color (from the base set) is the result.

 

If you have more than one value you are looking for then you would need to provide a more complicated example of the data - as a data step- and the expected results.

Reeza
Super User
data have;
input ID colour $;
cards;
1         Blue
1         Green
1         Red
2         Blue
2         Yellow
3         Green
3         Red
3         Yellow
;;;;
run;

proc sort data=have;
by id colour;
run;


data red_list;
set have;
where colour="Red";
by ID;
if first.ID;
rename colour=master_colour;
run;

data want;
merge have red_list;
by ID;
final_colour = coalescec(master_colour, colour);
run;

@ballardw solution but a data step approach instead. These are probably the best approaches given your question. It's possible to do this in a single step using DoW if efficiency becomes an issue but that's a more complex programming approach.

BillSut
Calcite | Level 5
Thank you very much. As usual I was making the problem more complicated than it was. Your solution is simple but elegant and makes perfect sense.
andreas_lds
Jade | Level 19

A hash object could be used:

 data want;
   set have;
   
   if _n_ = 1 then do;
      declare hash h(dataset: 'have(where= (Color = "Red"))');
      h.defineKey('id');
      h.defineData('color');
      h.defineDone();
   end;
   
   rc = h.find();
   drop rc;
 run;

Maybe a bit to hefty for the problem 😉

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 572 views
  • 1 like
  • 5 in conversation