topic Re: Top Values based on other variables in SAS Programming

Top Values based on other variables

Sotarkadin — Wed, 03 Oct 2018 18:58:38 GMT

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

Re: Top Values based on other variables

Astounding — Wed, 03 Oct 2018 19:02:08 GMT

What if there is a five-way tie for third place. What do you want to do with the 5 tied observations?

Re: Top Values based on other variables

Sotarkadin — Wed, 03 Oct 2018 19:03:47 GMT

If there is a tie I would like just the first one of the ties.

Re: Top Values based on other variables

novinosrin — Wed, 03 Oct 2018 19:06:17 GMT


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;

Re: Top Values based on other variables

novinosrin — Wed, 03 Oct 2018 19:12:08 GMT

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;

Re: Top Values based on other variables

Sotarkadin — Wed, 03 Oct 2018 19:12:57 GMT

@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.