MULTIPLE Qs- Top5/Bottom5 & Comparison of Variables

[ccherrub]

I have a couple of questions and let me know if I need to elaborate more!

1.) How can I get the top 5 and the bottom 5 observations of a (categorical) value while also showing the percentages ?

2.) How do I make it so a large piece of data where it's trying to find a relationship, download to excel? So far I've used:

proc compare base=work.details;
var income;
with ed;
title 'Comparison of Variables in the Same Data Set';
run;

However, it doesn't translate to Excel so there's not a way for me to make a graph.

[PaigeMiller]

1) Do you mean categories with the top 5 frequencies, or top 5 percents?

2) "How do I make it so a large piece of data where it's trying to find a relationship, download to excel?" Explain further, I don't understand.

[ccherrub]

1.) So I'm trying to get the top/bottom 5 states in regards to how many observations are in each state and the percentage out of the whole in regards to observations as well. I.e.
STATES OBSVERATIONS PERCENTAGES
NY 2378 2%
TX 45678 3.8%
CA 100 .0008%

Or something like that. (These aren't my actual numbers, I tried to make them as proportional as possible if that matters)

2.) When I used the code in the initial post, it gave me relationship data but it was in a way that didn't make it possible for me to download it to excel or use excel to find a relationship chart. I'm pretty certain it's the code I used. I'll attach the question I'm studying for:

Use a graph of your choice (i.e., not a table) to display (and compare) the average income of individuals with different level of education. You may use Excel to create your graph once you extracted the necessary information from SAS. Include this graph in your write-up and discuss it.

[PaigeMiller]

1) You want to use PROC FREQ on your data, with the option ORDER=FREQ

2) You want to produce a scatter plot, you can use PROC SGPLOT with the SCATTER statement

[Rick_SAS]

It sounds like you want the Top and Bottom 5 categories by percentage. See that article, "An easy way to make a "Top 10" table and bar chart in SAS."

Here is an example of how to use the technique to get the Top and Bottom 5:

/* write the sorted frequencies to a data set */
proc freq data=sashelp.cars ORDER=FREQ noprint;
  table make / out=FreqOut;
run;

/* print the top 5 */
%let MaxN = 5;
title "Top &MaxN Categories";
proc print data=FreqOut(obs=&MaxN);
run;

/* print the bottom 5 */
proc sort data=FreqOut; by percent; run;
title "Bottom &MaxN Categories";
proc print data=FreqOut(obs=&MaxN);
run;

[sbxkoenk]

Hello,

For 1.) , see these two blogs :

Selecting the top n% and bottom n% of observations from a data set
By Kathryn McLawhorn on SAS Users July 21, 2017
https://blogs.sas.com/content/sgf/2017/07/21/selecting-the-top-n-and-bottom-n-of-observations-from-a...

4 ways to find the k smallest and largest data values in SAS
By Rick Wicklin on The DO Loop January 26, 2022
https://blogs.sas.com/content/iml/2022/01/26/k-smallest-largest-data.html

Koen