good Day,
I currently have a SAS create table proc where I do count of how many times a certain value occurs based on another value.
For example in the data below there is a count for how many times the field value occured within the field. For field AB123 the value Note 1 appeared 100 times in teh database and etc. I want to pull the top 3 values for each field. Based on the table below for AB123 i should get the data for the values of Note 1, Note 2 and Note 3. I hope this makes sense.
Field | field_value | cnt_val |
AB123 | Note 1 | 100 |
AB123 | Note 2 | 90 |
AB123 | Note 3 | 46 |
AB123 | Note 4 | 23 |
AB123 | Note 5 | 12 |
AB123 | Note 6 | 10 |
AB123 | Note 7 | 5 |
BC234 | Note 1 | 1065 |
BC234 | Note 2 | 456 |
BC234 | Note 3 | 231 |
BC234 | Note 4 | 153 |
BC234 | Note 5 | 100 |
BC234 | Note 6 | 56 |
data have;
input Field $ field_value & $ cnt_val;
cards;
AB123 Note 1 100
AB123 Note 2 90
AB123 Note 3 46
AB123 Note 4 23
AB123 Note 5 12
AB123 Note 6 10
AB123 Note 7 5
BC234 Note 1 1065
BC234 Note 2 456
BC234 Note 3 231
BC234 Note 4 153
BC234 Note 5 100
BC234 Note 6 56
;
proc rank data=have out=want(where=(cnt_val_rank<=3)) ties=low descending;
by field notsorted;
var cnt_val ;
ranks cnt_val_rank ;
run;
What if there is a five-way tie for third place. What do you want to do with the 5 tied observations?
If there is a tie I would like just the first one of the ties.
data have;
input Field $ field_value & $ cnt_val;
cards;
AB123 Note 1 100
AB123 Note 2 90
AB123 Note 3 46
AB123 Note 4 23
AB123 Note 5 12
AB123 Note 6 10
AB123 Note 7 5
BC234 Note 1 1065
BC234 Note 2 456
BC234 Note 3 231
BC234 Note 4 153
BC234 Note 5 100
BC234 Note 6 56
;
proc rank data=have out=want(where=(cnt_val_rank<=3)) ties=low descending;
by field notsorted;
var cnt_val ;
ranks cnt_val_rank ;
run;
or just
proc sort data=have;
by field descending cnt_val;
run;
data want;
do _n_=1 by 1 until( last.field);
set have;
by field ;
if _n_<=3 then output;
end;
run;
@novinosrin Thank you so much. This worked perfectly. I was trying to use the Rank function and was unable to get it to work properly.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.