How to rank rows in sas dataset

[Lohia]

Hello Guys,

Could you please help to get the below problem solved:

STATE  CITY   VALUE  COUNT

A            1          F           1

A             2         F           1

A            3          G           2

B            4         G            1

B            5          H           2

B            6          H           2

C           7          J            1

C           8          K            2

C           9         F             3

I need to rank the values , as given in count variable.

Thanks!

Parveen

[Reeza]

http://www.ats.ucla.edu/stat/sas/faq/enumerate.htm

Your BY groups appear to be STATE & VALUE

[Lohia]

Hi Reeza,

Its showing as below:

STATE  CITY   VALUE  COUNT   Your_sol

A            1          F           1            1

A             2         F           1            2

A            3          G           2           1

B            4         G            1            1

B            5          H           2             1

B            6          H           2            2

C           7          J            1            1

C           8          K            2            1

C           9         F             3           1

Thanks!

Parveen

[Reeza · Accepted solution]

Yeah, without seeing your code I can't comment. 

Use @Ksharp solution, because your data isn't sorted in a standard manner, in your last group the J/K/F grouping would be incorrect. 

The suggested (incorrect) code based on the link should have been:

data want;
set have;
by state value;
retain your_sol;

if first.state then your_sol=0;
if first.value then your_sol+1;

run;

[Ksharp]

OK. Assumiing your data has been sorted like you showed.

data have;
input STATE $ CITY   VALUE  $;
cards;
A            1          F        
A             2         F       
A            3          G        
B            4         G      
B            5          H    
B            6          H     
C           7          J        
C           8          K    
C           9         F 
;
run;
data want;
 set have;
 by  STATE  VALUE notsorted;
 if first.STATE then count=0;
 count+first.VALUE;
run;

[Lohia]

Thanks a lot Reeza and Ksharp . Really helpful guys 🙂