topic Re: How do I rank by group within a variable while retaining rank for one observation? in SAS Programming

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

Ani7 — Tue, 31 Jul 2018 13:02:17 GMT

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.

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

PeterClemmensen — Tue, 31 Jul 2018 13:06:32 GMT

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

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

Ani7 — Tue, 31 Jul 2018 14:08:42 GMT

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.

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

Ksharp — Tue, 31 Jul 2018 14:10:30 GMT

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;

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

Ani7 — Tue, 31 Jul 2018 14:52:24 GMT

Yup! That helps out big time. Thanks!

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

gamotte — Tue, 31 Jul 2018 15:24:04 GMT

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;