Running Count

[lloraine]

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.

[ChrisHemedinger]

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

[lloraine]

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?

[LinusH]

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

[lloraine]

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

[AaroninMN]

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;

[p12937]

proc sort data=group_zip;

by Group ZIP;

run;

data first_zip;

set group_zip;

by Group ZIP;

  if first.Group;

run;

[lloraine]

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.

[p12937]

i misunderstood your post.

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

[lloraine]

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

[Reeza]

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.

[TimCampbell]

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.

[lloraine]

Thank you, I will try this.