How do I rank by group within a variable while retaining rank for one observation?

[Ani7]

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.

[PeterClemmensen]

Is Sam always the first name in a group of IDs?

[Ani7]

No, Sam can be in any location in the data that I have. I want it to be the first name with a Rank of 1 at the end of the process.

[Ksharp · Accepted solution]

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;

[Ani7]

Yup! That helps out big time. Thanks!

[gamotte]

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;