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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.