## How to rank rows in sas dataset

Solved
Occasional Contributor
Posts: 18

# 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: 23,683

## 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;``````

All Replies
Super User
Posts: 23,683

## 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: 23,683

## 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,766

## 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 and locked.

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

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