topic How to do a cumulative count based on multiple groups in SAS Enterprise Guide

How to do a cumulative count based on multiple groups

crawfe — Fri, 12 Apr 2019 13:19:07 GMT

Hello. I am trying to get a table of cumulative Counts grouped by

two variables, a Category UpNum and a Date (M_Y). Do you have any

code ideas? Thanks!!

Input Table Desired Output

UpNum M_Y UpNum M_Y M_Ycount cum_Count

XXA 1/2018 XXA 1/2018 1 1

XXA 4/2018 XXA 2/2018 0 1

XXB 2/2018 XXA 3/2018 1 2

XXB 2/2018 XXA 4/2018 1 3

XXA 3/2018 ..... ...... (list of all XXA, M_Y combinations)

XXB 4/2018 XXB 1/2018 0 0

XXA 6/2018 XXB 2/2018 2 2

..... ...... XXB 3/2018 0 2

( XXC, XXD, all M_Y dates, etc) XXB 4/2018 1 3

....... ....... (list of all XXB, M_Y combinations)

Re: How to do a cumulative count based on multiple groups

novinosrin — Fri, 12 Apr 2019 20:34:22 GMT

@crawfe The sparse option in proc freq will give you the combination UpNum*M_Y

You can use the output of proc freq for as input in one datastep pass. The datastep is rather straight forward, the only thought process here is cartesian either using SQL or Sparse etc to get the combination


                                   
data have;
input UpNum $    M_Y    $  ; 
cards;
XXA        1/2018                             
XXA         4/2018                             
XXB         2/2018                            
XXB         2/2018                                   
XXA        3/2018                        
XXB         4/2018                           
XXA         6/2018 
;
proc freq data=have noprint;
table UpNum*M_Y/out=temp(drop=percent) sparse ;
run;

Now use the temp dataset, merge back with have with IN=dataset options for your cumulative count

Re: How to do a cumulative count based on multiple groups

Ksharp — Fri, 12 Apr 2019 14:36:46 GMT

Using nov's code .

                                  
data have;
input UpNum $    M_Y    $  ; 
cards;
XXA        1/2018                             
XXA         4/2018                             
XXB         2/2018                            
XXB         2/2018                                   
XXA        3/2018                        
XXB         4/2018                           
XXA         6/2018 
;
proc freq data=have noprint;
table UpNum*M_Y/out=temp(drop= percent) sparse ;
run;
data want;
 set temp;
 by upnum ;
 if first.upnum then want=0;
  want+count;
run;

Re: How to do a cumulative count based on multiple groups

crawfe — Fri, 12 Apr 2019 18:54:03 GMT

That worked well. Thanks!