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

Grouped by ID, I would like to assign a 'rank' variable to the 'animal' variable.   The trick is the values assigned for the new 'rank' variable may vary pending on the which animal variables are included within each ID grouping.

For examples, I have the below data set.  If both "puppy" and "dog" are present within the same ID group, then dog=1 and puppy = 2 (See ID A or C).  If only dog or only puppy is present regardless of the other animals present, then the value for either dog or puppy = 1 (See ID B or D).

data want;

input      ID animal rank;

             A  dog     1

             A  bird     1

             A  puppy  2

             B  bird     1

             B  puppy  1

             C  dog     1

             C  puppy  2

             D  dog     1

             D  cat      1;

run;

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

OK.

data want;
input      ID $ animal $;
cards;
             A  dog     
             A  bird     
             A  puppy  
             B  bird   
             B  puppy  
             C  dog   
             C  puppy 
             D  dog  
             D  cat  
;
run;
data want;
 do until(last.id);
  set want;
  by id;
  if animal='dog' then dog=1;
   else if animal='puppy' then puppy=1;
 end;
  do until(last.id);
  set want;
  by id;
  if dog and puppy then do;
                          if animal='puppy' then rank=2;
                                 else rank=1;
                              end;
    else if dog or puppy then rank=1;
  output;
 end;
 run;

Ksharp

View solution in original post

4 REPLIES 4
Ksharp
Super User

OK.

data want;
input      ID $ animal $;
cards;
             A  dog     
             A  bird     
             A  puppy  
             B  bird   
             B  puppy  
             C  dog   
             C  puppy 
             D  dog  
             D  cat  
;
run;
data want;
 do until(last.id);
  set want;
  by id;
  if animal='dog' then dog=1;
   else if animal='puppy' then puppy=1;
 end;
  do until(last.id);
  set want;
  by id;
  if dog and puppy then do;
                          if animal='puppy' then rank=2;
                                 else rank=1;
                              end;
    else if dog or puppy then rank=1;
  output;
 end;
 run;

Ksharp

sophia_SAS
Obsidian | Level 7

Thanks!  This is very helpful!

art297
Opal | Level 21

: I may have misunderstood what you are trying to achieve but, if not, I think the following (untested) change to 's code will get you what you want:

data want;

do until(last.id);

  set want;

  by id;

if animal='dog' then dog=1;

  else if animal='puppy' then puppy=1;

end;

  do until(last.id);

  set want;

  by id;

  if dog and puppy and animal='puppy' then rank=2;

else rank=1;

output;

end;

run;

PGStats
Opal | Level 21

A bit simpler...

data have;
input ID $ animal $;
datalines;
A dog
A bird
A puppy
B bird
B puppy
C dog
C puppy
D dog
D cat
;

data want;

do until (last.ID);

     set have;

     by ID;

     dogPresent = dogPresent or lowcase(animal)="dog";

     end;

do until (last.ID);

     set have;

     by ID;

     rank = 1 + (lowcase(animal)="puppy" and dogPresent);

     output;

     end;

drop dogPresent;

run;

PG

PG

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
  • 4 replies
  • 894 views
  • 4 likes
  • 4 in conversation