topic Re: Creating running total in EG (Top N) in SAS Enterprise Guide

Creating running total in EG

deleted_user — Tue, 29 Jan 2008 20:04:20 GMT

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

Re: Creating running total in EG

deleted_user — Tue, 29 Jan 2008 20:27:30 GMT

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

Re: Creating running total in EG

deleted_user — Tue, 29 Jan 2008 20:34:48 GMT

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

Re: Creating running total in EG

deleted_user — Tue, 29 Jan 2008 20:47:34 GMT

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.

Re: Creating running total in EG

ChrisHemedinger — Thu, 07 Feb 2008 15:00:02 GMT

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

Re: Creating running total in EG (Top N)

ChrisHemedinger — Fri, 29 Aug 2008 02:10:00 GMT

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

Re: Creating running total in EG (Top N)

Patrick — Fri, 29 Aug 2008 06:39:38 GMT

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