topic Re: How to rank rows in sas dataset in SAS Programming

How to rank rows in sas dataset

Lohia — Fri, 29 Apr 2016 07:11:34 GMT

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

Re: How to rank rows in sas dataset

Reeza — Fri, 29 Apr 2016 07:26:13 GMT

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

Your BY groups appear to be STATE & VALUE

Re: How to rank rows in sas dataset

Ksharp — Fri, 29 Apr 2016 07:28:44 GMT

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;

Re: How to rank rows in sas dataset

Lohia — Fri, 29 Apr 2016 07:51:48 GMT

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

Re: How to rank rows in sas dataset

Reeza — Fri, 29 Apr 2016 07:57:19 GMT

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;

Re: How to rank rows in sas dataset

Lohia — Fri, 29 Apr 2016 09:33:59 GMT

Thanks a lot Reeza and Ksharp . Really helpful guys 🙂