Desktop productivity for business analysts and programmers

Creating running total in EG

Not applicable
Posts: 0

Creating running total in EG

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;

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;

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

Message was edited by: DavidW
Not applicable
Posts: 0

Re: Creating running total in EG

Posted in reply to deleted_user
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
Not applicable
Posts: 0

Re: Creating running total in EG

Posted in reply to deleted_user
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?
Not applicable
Posts: 0

Re: Creating running total in EG

Posted in reply to deleted_user
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.
Community Manager
Posts: 3,462

Re: Creating running total in EG

Posted in reply to deleted_user
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.

/* replace these values with your data/columns */
%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;

/* 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 Smiley Surprisedverall,:numobs
from summary where _TYPE_=0;
select count(distinct &category) into :categorycount from summary;

/* 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;

/* 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;
if rank le &n then output;

/* 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;

Community Manager
Posts: 3,462

Re: Creating running total in EG (Top N)

Posted in reply to ChrisHemedinger
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.

Respected Advisor
Posts: 4,795

Re: Creating running total in EG (Top N)

Posted in reply to ChrisHemedinger
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:



Ask a Question
Discussion stats
  • 6 replies
  • 3 in conversation