BookmarkSubscribeRSS Feed
deleted_user
Not applicable
I am new to EG although I am an experienced SAS user. I have a code that I wrote to identify top 50 observations based on my sort criteria. I would like to know how I can accomplish this task in EG without writing the code. Here is the code:

proc sort data=A;
by X Y Z;
run;

data top50;
set A;
by X Y Z;
retain count;
if first.X then count=1;
else count = count+1;
if count <=50;
drop count;
run;

Thanks for your help! if count <=50;
drop count;
run;




Message was edited by: DavidW
6 REPLIES 6
deleted_user
Not applicable
I do this a lot, especially when I need to limit a transpose to 20 things for charting.

1) Use a query to select the columns and basic values/things of interest
2) In the query's sort tap, add the value/thing of interest and set it to descending sort order
3) In the upper rightish area of the query dialog box/window is a drop list/button titled "options"; Select "Options for this Query".
4) In the resulting "Results Options" popup dialog box, near the bottom, select the "Limit output rows:" check box, and put the number of rows desired to be returned in the edit field on the right.
5) Click "Ok" then click "Run"

Let's say you create a query like the following:

select host, command, datetime, max(utilization) as max_of_utilization
from performance_metrics
group by host, command, datetime;

and you want the top 50 commands with the most utilization.
This is easily created in the query form (no code) including the addition of

sort by max_of_utilization descending;

In the query dialog box, you add utilization to the "Select Data" tab and then under the summary column select "MAX" and SAS EG will add a "Computed Column" to the list of columns available. Then on the "Sort Data" tab you add "MAX_OF_Utilization" and select Descending in its "Sort Direction" column.

Now you can change the options for that query to limit output to 50 rows.

With that, you can now create a new query to join back to the original dataset and act as filter to select all the records related to the selected top50 commands.

To help with the join, I would recommend you change the name of the output from the select top 50 query to "TOP50_COMMANDS". This is done by clicking on the "CHANGE" button at the top of the query box for the "Output name:"

Now with the new join query from the originating dataset, you can add a table, from the current project, and easily find "TOP50_COMMANDS" in the list.

Hopefully you should be able to find the rest of your way from here. Message was edited by: Chuck
deleted_user
Not applicable
Chuck,
Thanks for the pointer. This will work only if I want 50 observations in total. What I am trying to do is to get top 50 observations for each value of X. If there are 5 distinct values of X I will have a total of 250 observations. Is there a way to accomplish this?
Thanks
deleted_user
Not applicable
Off the top of my head, what you are doing in code is the simplest and most elegant way.

If you want to figure out a way with a Query dialog, you'll have to root around with the box, its options, calculated fields, etc. to see if you can figure out a way.

There is a very complicated method that comes to mind; you query for the unique X values; then, new queries, filtering for those explicit X values, resulting in your top 50's; then a final query that adds the resulting tables together. But this is very ugly and painful.
ChrisHemedinger
Community Manager
Here's another code-based way. It's not as short and sweet, but it's versatile. You just need to modify the top portion to set the macro variables to your data and column names.

[pre]
/* replace these values with your data/columns */
%let data=SASHELP.PRDSALE;
%let report=PRODUCT;
%let measure=ACTUAL;
%let measureformat=%str(format=DOLLAR12.2); /* to retain measure format in report */
%let stat=SUM; /* or use MEAN, for average */
%let n=50; /* max values to report per category */
%let category=COUNTRY;
title Top PRODUCTs based on ACTUAL Sales in each COUNTRY;
footnote Generated on &SYSDATE;


/* summarize the data across a category and store */
/* the output in an output data set */
proc means data=&data &stat noprint;
var &measure;
class &category &report;
output out=summary &stat=&measure &category /levels;
run;

/* store the value of the measure for ALL rows and
/* the row count into a macro variable for use */
/* later in the report */
proc sql noprint;
select &measure,_FREQ_ into :overall,:numobs
from summary where _TYPE_=0;
select count(distinct &category) into :categorycount from summary;
quit;

/* sort the results so that we get the TOP values */
/* rising to the top of the data set */
proc sort data=work.summary out=work.topn;
where _type_>2;
by &category descending &measure;
run;

/* Pass through the data and output the first N */
/* values for each category */
data topn;
length rank 8;
label rank="Rank";
set topn;
by &category descending &measure;
if first.&category then rank=0;
rank+1;
if rank le &n then output;
run;

/* Create a report listing for the top values in each category */
footnote2 "&stat of &measure for ALL values of &report: &overall (&numobs total rows)";
proc report data=topn;
columns &category rank &report &measure;
define &category /group;
define rank /display;
define &measure / analysis &measureformat;
run;
quit;
[/pre]

Chris
It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
ChrisHemedinger
Community Manager
Here is a more formal example for creating Top N reports in SAS and in SAS Enterprise Guide. The example includes SAS programs, and also includes a custom task for EG so you can avoid the programming, if you want.

http://support.sas.com/kb/33/009.html

Chris
It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
Patrick
Opal | Level 21
Hi David
Like you I'm an experienced coder and a bit resistive to use EG. So this was a nice challenge.

What worked was righ clicking the SAS table in the project explorer window and then use „Filter and Query...“. I „pointed-and-clicked“ the following code:

%_eg_conditional_dropds(WORK.Query_for_HAVE);

PROC SQL OUTOBS=50;
CREATE TABLE WORK.Query_for_HAVE AS SELECT HAVE.i,
(COUNT(HAVE.i)) AS COUNT_OF_i
FROM WORK.HAVE AS HAVE
GROUP BY HAVE.i
ORDER BY COUNT_OF_i DESCENDING;
QUIT;

Regards
Patrick

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 2895 views
  • 0 likes
  • 3 in conversation