Hi, new SAS programmer here using SAS EG. The data I have is in the following format:
User_ID | Name | Volume
A12 | John | 345
A12 | Sam | 243
A12 | Rain | 120
A12 | Carl | 5
A12 | Joe | 10
A12 | Bain | 600
A32 | Sam | 243
A32 | Rain | 450
I am trying to create a rank variable that ranks Name by descending volume for each User_ID such that Sam always retains the number 1 rank. So the data I want is:
User_ID | Name | Volume | Rank
A12 | Sam | 243 | 1
A12 | Bain | 600 | 2
A12 | John | 345 | 3
A12 | Rain | 120 | 4
A12 | Joe | 10 | 5
A12 | Carl | 5 | 6
A32 | Sam | 243 | 1
A32 | Rain | 450 | 2
The code I am using is:
data have2;
set have1;
if Name = "Sam" then Rank = 1;
proc sort;
by descending Rank descending Volume ;
run;
data have3;
set have2;
retain Rank1;
Rank1 + 1;
run;
data want;
set have3;
if missing(Rank) then Rank = Rank1;
drop Rank1;
proc sort;
by Name;
run;
This seems to work only in situations where I am considering one User_ID. I need to apply this to about 100k User_IDs.
Why not make a dummy variable ?
data have2;
set have1;
if Name = "Sam" then _Volume = 99999999;
else _Volume=Volume;
run;
proc sort;
by descending Rank descending _Volume ;
run;
Is Sam always the first name in a group of IDs?
Why not make a dummy variable ?
data have2;
set have1;
if Name = "Sam" then _Volume = 99999999;
else _Volume=Volume;
run;
proc sort;
by descending Rank descending _Volume ;
run;
Yup! That helps out big time. Thanks!
Hello,
You can use "CASE WHEN" in proc sql to define a custom sort order :
data have;
input User_ID $ Name $ Volume;
cards;
A12 John 345
A12 Sam 243
A12 Rain 120
A12 Carl 5
A12 Joe 10
A12 Bain 600
A32 Sam 243
A32 Rain 450
;
run;
proc sql;
CREATE TABLE have_s AS
SELECT *
FROM have
ORDER BY User_Id, CASE WHEN upcase(NAME)="SAM" THEN 1 ELSE 2 END, VOLUME DESCENDING
;
quit;
data want;
set have_s;
by User_Id;
retain rank;
if first.User_Id then rank=1;
else rank=rank+1;
run;
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.