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!
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
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
Thanks! This is very helpful!
: 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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.