Ranking Count of Occurences in Sub Groups

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

Ranking Count of Occurences in Sub Groups

I have a table for which I need to generate the Code_Count_Rank column shown here, which is ranking the count of code occurances for each person; note that once a code has been ranked for a given person, subsequent lines with that code on it need not be ranked; aso note that when two ranks are the same, it doesn't matter which one gets the higher/lower ranking.

 

 

SAS TABLE.png


Accepted Solutions
Solution
‎11-01-2016 06:53 PM
Respected Advisor
Posts: 4,998

Re: Ranking Count of Occurences in Sub Groups

That makes it relatively easy then:

 

proc sort data=have;

by name code;

run;

 

data want;

set have;

by name code;

if first.name then temp_counter=0;

if first.code then do;

   temp_counter + 1;

   code_count_rank = temp_counter;

end;

drop temp_counter;

run;

 

It actually adds a little complexity to blank out the CODE_COUNT_RANK values for the duplicates.

 

View solution in original post


All Replies
Respected Advisor
Posts: 4,998

Re: Ranking Count of Occurences in Sub Groups

Are you allowed to sort your data set?  It makes a significant difference in the complexity level.

Contributor
Posts: 20

Re: Ranking Count of Occurences in Sub Groups

Yeah, sorting is no problem. 

Solution
‎11-01-2016 06:53 PM
Respected Advisor
Posts: 4,998

Re: Ranking Count of Occurences in Sub Groups

That makes it relatively easy then:

 

proc sort data=have;

by name code;

run;

 

data want;

set have;

by name code;

if first.name then temp_counter=0;

if first.code then do;

   temp_counter + 1;

   code_count_rank = temp_counter;

end;

drop temp_counter;

run;

 

It actually adds a little complexity to blank out the CODE_COUNT_RANK values for the duplicates.

 

Contributor
Posts: 20

Re: Ranking Count of Occurences in Sub Groups

Thanks for the help. What step here is blanking out the duplicates?
Respected Advisor
Posts: 4,998

Re: Ranking Count of Occurences in Sub Groups

Copying from TEMP_COUNTER to CODE_RANK_COUNT blanks out the duplicates.  That copying only takes place for the first observation per NAME/CODE.  It's easier to see if you get rid of the DROP sttatement and examine both counters.

☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 335 views
  • 1 like
  • 2 in conversation