topic Re: Selecting Top 10 records based on the variable in Price freq in SAS Programming

Selecting Top 10 records based on the variable in Price freq

pappusrini — Thu, 07 Jun 2018 20:17:47 GMT

How to select Top 10 records based on the variable in Proc Freq? Thanks in advance

I did the order = freq however I’m trying to compare the metrics by Week...when I do the order = freq it gives me in descending but not the weeks in order (week1, week2, week3) etc. also how do I choose the top 10 counts?

Please advise. Thank you

Re: Selecting Top 10 records based on the variable in Price freq

ballardw — Thu, 07 Jun 2018 20:53:31 GMT

What do you mean by "choose"? Are you attempting to create a subset of the data?

How do we know what a "week" might be? Do you have a variable named week? Dates that have to be assigned to a week?

You should provide a small example of data as you have now and then show what the desired result would be.

You might try 1) sort your data by week and 2) run your proc freq with a BY week. That should get the order within each week value, if that is what you have.

Your request is not quite clear because you could be wanting just the counts or sub-setting the data to get the records that provided a count.

You could demonstrate the logic with fewer records if using "top 3" instead of "top 10".

Re: Selecting Top 10 records based on the variable in Price freq

Astounding — Thu, 07 Jun 2018 21:34:36 GMT

Here's a possibility based on a few reasonable guesses as to what the results should be:

proc freq data=have;

tables week * metric / noprint out=counts;

run;

proc sort data=counts;

by week descending count;

run;

data want;

set counts;

by week;

if first.week then rank = 1;

else rank + 1;

if rank <= 10;

keep week metric count;

run;

Re: Selecting Top 10 records based on the variable in Price freq

Ksharp — Fri, 08 Jun 2018 12:43:50 GMT

@Rick_SAS just wrote a blog about it . But you need the newest SAS version.

proc freq data=have order=freq;

table x/ maxlevels=10;

run;

Re: Selecting Top 10 records based on the variable in Price freq

Rick_SAS — Fri, 08 Jun 2018 13:19:57 GMT

The article "An easy way to make a "Top 10" table and bar chart in SAS" shows how.

@Ksharp is mistaken about needing the latest version of SAS. This option is available in any version of SAS 9.4, and SAS 9.4M0 shipped way back in in 2013.

If you aren't using SAS 9.4, there is a link in the blog post to an older technique.