topic Re: Running Count in SAS Enterprise Guide

Running Count

lloraine — Thu, 21 Mar 2013 12:22:34 GMT

I need to do a running count. How do you do this in SAS Enterprise Guide 4.3? I need specifics as I am fairly new to this tool. Thank you.

Re: Running Count

ChrisHemedinger — Thu, 21 Mar 2013 13:07:41 GMT

I have a custom task for Calculate Running Totals. You can download it at:

Custom Tasks for SAS Enterprise Guide using Microsoft .NET

See the Chapter 11 example. You can download the Binaries and install the task per the instructions in the free chapter of my book.

Alternatively, you can achieve this fairly easily with DATA step. See this SAS Note for an example.

Chris

Re: Running Count

lloraine — Thu, 21 Mar 2013 13:27:12 GMT

I need a running count not total. In another tool, I use running-count(zipcode for Group number). I call the calculated column runningctzip. It counts the number of zipcodes for a group number - 1,2 etc. Then I say runningctzip = 1 because I only want the first one. Anything like that in SAS?

Re: Running Count

LinusH — Thu, 21 Mar 2013 13:44:44 GMT

Can you please provide some examples/sample data...?

Re: Running Count

lloraine — Thu, 21 Mar 2013 13:52:02 GMT

Group Number Zip Calculated column - runningct(zip for Group Number) called ZipCnt
01 15108 1

01 15233 2

02 12222 1

03 13333 1

03 12344 2

03 13456 3

Since I only want the first Zip, I would say ZipCnt =1

Re: Running Count

AaroninMN — Thu, 21 Mar 2013 15:27:53 GMT

This loop might get you close.

sort data=Table;

by Group_Number ZIP_Code;

H=1;

Run;

data chk;

set Table;

by Group_Number ZIP_Code;

if first.Group_Number and first.ZIP_Code then sub_total =0;

if last.Group_Number;

run;

Re: Running Count

p12937 — Thu, 21 Mar 2013 16:00:57 GMT

proc sort data=group_zip;

by Group ZIP;

run;

data first_zip;

set group_zip;

by Group ZIP;

if first.Group;

run;

Re: Running Count

lloraine — Thu, 21 Mar 2013 16:01:49 GMT

I just thought there was some simple way to do this in Enterprise Guide. Would I have to open a program to run this? I don't understand the logic. I am very new to SAS. I just want a calculated column that counts the zip codes for Group Number.

Re: Running Count

p12937 — Thu, 21 Mar 2013 16:06:38 GMT

i misunderstood your post.

i guess you could just run a frequency on group then.

Re: Running Count

lloraine — Thu, 21 Mar 2013 16:16:28 GMT

What is a frequency? That is how new to this I am.

Re: Running Count

Reeza — Thu, 21 Mar 2013 16:23:45 GMT

Add your data to your project.

Go to Tasks>Describe.

Try a few of the procedures to see what they give you. You're probably looking for a table analysis or a one way freq.

If you really only want the first record of a data set then look at TASKS>DATA>SORT. Under the options for the Sort procedure you can keep just the first of each sorted field.

Re: Running Count

TimCampbell — Mon, 25 Mar 2013 15:08:30 GMT

Hi lloraine,

there is a function called monotonic() that might help you.

If you right click on your data and choose query builder in EG then do the following,

add the GroupNumber and Zip columns to the select data tab

add a computed column as an advanced expression and type monotonic() as the expression.

call the column 'rank' or something clear for you.

back in the main window add your 'rank' column a second time and choose MIN from the Summary drop down.

make sure that the summary groups are only on Group number (this is important)

(see attached screenshot)

when you run the task your output data should have a rank and a min_of_rank column.

if you filter this data for where the two columns are equal that should give you what you want.

you will probably need to do it as two steps since it seemd to complain when i tried to put the filter in in the same query.

Hope this helps.

Tim.

Re: Running Count

lloraine — Mon, 25 Mar 2013 15:48:16 GMT

Thank you, I will try this.