Desktop productivity for business analysts and programmers

Running Count

Reply
Contributor
Posts: 58

Running Count

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.

Community Manager
Posts: 2,889

Re: Running Count

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

Contributor
Posts: 58

Re: Running Count

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?

Super User
Posts: 5,386

Re: Running Count

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

Data never sleeps
Contributor
Posts: 58

Re: Running Count

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

Occasional Contributor
Posts: 15

Re: Running Count

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;

Occasional Contributor
Posts: 8

Re: Running Count

proc sort data=group_zip;

by Group ZIP;

run;

data first_zip;

set group_zip;

by Group ZIP;

  if first.Group;

run;

Contributor
Posts: 58

Re: Running Count

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.

Occasional Contributor
Posts: 8

Re: Running Count

i misunderstood your post.

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


Contributor
Posts: 58

Re: Running Count

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

Super User
Posts: 19,157

Re: Running Count

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.

Contributor
Posts: 45

Re: Running Count

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.


query builder.PNG
Contributor
Posts: 58

Re: Running Count

Thank you, I will try this.

Ask a Question
Discussion stats
  • 12 replies
  • 829 views
  • 0 likes
  • 7 in conversation