DATA Step, Macro, Functions and more

How to rank rows in sas dataset

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

How to rank rows in sas dataset

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

 


Accepted Solutions
Solution
‎04-29-2016 05:31 AM
Super User
Posts: 19,868

Re: How to rank rows in sas dataset

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;

View solution in original post


All Replies
Super User
Posts: 19,868

Re: How to rank rows in sas dataset

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

 

Your BY groups appear to be STATE & VALUE

Occasional Contributor
Posts: 18

Re: How to rank rows in sas dataset

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

Solution
‎04-29-2016 05:31 AM
Super User
Posts: 19,868

Re: How to rank rows in sas dataset

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;
Super User
Posts: 10,044

Re: How to rank rows in sas dataset

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;
Occasional Contributor
Posts: 18

Re: How to rank rows in sas dataset

Thanks a lot Reeza and Ksharp . Really helpful guys :-) 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 403 views
  • 0 likes
  • 3 in conversation