Adding a column of cumulative counts in SAS for subgroups within a larger list

hantran99
hantran99 used Ask the Experts™
on
Hi anyone,

Just wondering if anyone has SAS code to add a column of cumulative counts

Example of what I am after.
Cust Count  Cum Count
Peter 1  1
Peter 4 5
Peter 5 10
Peter 6 16
Peter 7 23
Janet 1 1
Janet 3 4
Janet 4 7
Mary 2 2
Mary 5 7
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
assuming your data is stored in dataset x, and x is already sorted by cust field:
data y;
  set x;
  by cust;
  length cumulative 8.;
  retain cumulative;
  cumulative = sum(cumulative, count);
run;

don't have anything to test the code on, so if there's an error just post it back here :)
Below is the code to create a cum column.  Comparing my results to your example, I get a different cum for Janet.  I think it should be 8, not 7.  Good luck.

DATA ds;
    INPUT Cust $ Count;
    DATALINES;
Peter 1
Peter 4
Peter 5
Peter 6
Peter 7
Janet 1
Janet 3
Janet 4
Mary  2
Mary  5
;
RUN;

PROC SORT DATA=ds;
    BY Cust;
RUN;

DATA ds;
    SET ds;
    BY Cust;
    IF FIRST.Cust THEN Cum_Count=Count;
    ELSE Cum_Count=Cum_Count+Count;
    RETAIN Cum_Count;
RUN;

PROC PRINT DATA=ds; RUN;

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial