PROC SQL help to Group By Count of Records in increment of Week, Month

[buechler66]

Hi.  I have a dataset I'm reading in for testing purposes.  I hope you can help me with this problem.  It has three variables ID, FROM_DT, and CODE. 

For each ID I need to be able to count the number of records for a given increment of time using the FROM_DT variable (so by Week or Month for example) from between my start date (&BGN_DT) and my end date (&END_DT).  A requirement is that I need to use Proc SQL for this.  

Using PROC SQL, is there a way to Group By ID and count the number of records by a particular time increment.  Again, like for Week (Sunday to Saturday for Weeks 1 thru 52) and/or for Month (Jan to Dec)? 

Any help here would be greatly appreciated!

%LET BGN_DT='01JAN2019'D;
%LET BGN_DT='31JAN2019'D;

DATA HAVE;
INPUT ID $ FROM_DT : DATE9. CODE $;
FORMAT FROM_DT DATE9.;
DATALINES;
F23 01JAN2019 T234
F23 30JAN2019 1234
F23 21FEB2019 1234
F23 25FEB2019 Z234
F23 23FEB2019 X234
F23 18MAR2019 1234
F23 16MAR2019 1234
F23 21MAR2019 1234
F23 31MAR2019 1234
F23 21APR2019 1234
F23 17APR2019 1234
F23 29APR2019 1234
F23 13MAY2019 1234
F23 19MAY2019 1234
F23 04MAY2019 1234
F23 01JUN2019 T234
F23 30JUN2019 1234
F23 21AUG2019 1234
F23 25AUG2019 Z234
F23 23AUG2019 X234
F23 18SEP2019 1234
F23 16SEP2019 1234
F23 21SEP2019 1234
F23 30SEP2019 1234
F23 21NOV2019 1234
F23 17NOV2019 1234
F23 29NOV2019 1234
F23 13DEC2019 1234
F23 19DEC2019 1234
F23 04DEC2019 1234
S34 01JAN2019 A344
S34 30JAN2019 A344
S34 21FEB2019 2344
S34 25FEB2019 E344
S34 23FEB2019 2344
S34 18MAR2019 2344
S34 16MAR2019 B344
S34 21MAR2019 B344
S34 31MAR2019 B344
S34 21APR2019 2344
S34 17APR2019 2344
S34 29APR2019 C344
S34 13MAY2019 D344
S34 19MAY2019 2344
S34 04MAY2019 2344
S34 01JUN2019 A344
S34 30JUN2019 A344
S34 21JUL2019 2344
S34 25JUL2019 E344
S34 23JUL2019 2344
S34 18AUG2019 2344
S34 16AUG2019 B344
S34 21AUG2019 B344
S34 30AUG2019 B344
S34 21NOV2019 2344
S34 17NOV2019 2344
S34 29NOV2019 C344
S34 13DEC2019 D344
S34 19DEC2019 2344
S34 04DEC2019 2344
;
RUN;

[PaigeMiller]

You could use the INTNX function in PROC SQL to count the number of weeks/months since &BGN_DT.

It's also very easy to do in PROC FREQ or PROC SUMMARY

[buechler66]

Hi. Any idea what the INTNX function syntax would look like for my example in Proc Sql? Say for the Week example.  I need to somehow use both INTNX and the COUNT function I think because in the end I need a dataset with three variables; ID, WEEK_NUM (or MONTH_NAME), and the actual Count.

[PaigeMiller]

I should have said the INTCK function. Plenty of examples here:

https://documentation.sas.com/?docsetId=lefunctionsref&docsetTarget=p1md4mx2crzfaqn14va8kt7qvfhr.htm...

[ChrisNZ]

>A requirement is that I need to use Proc SQL for this.  

This seems like a silly requirement

> is there a way to Group By ID and count the number of records

Like this?

proc sql; 
select ID, sum( FROM_DT between &BGN_DT and &END_DT) as count
from HAVE
group by ID                                         ;

[PaigeMiller]

---

@ChrisNZ wrote:

>A requirement is that I need to use Proc SQL for this.  

This seems like a silly requirement

---

Agreed.

[buechler66]

Hmm, I would use the SUM() function?

[PGStats · Accepted solution]

Try

proc sql;
select 
    id,
    intnx("week", from_dt, 0) as week "Week starting on" format=yymmdd10.,
    count(*) as count
from have
where from_dt between &bgn_dt. and &end_dt.
group by id, calculated week;
quit;

replace week by month for monthly counts.

[buechler66]

Thank you so much for the example. Now I can see exactly what you mean. I appreciate your help and time so much!

[hashman]

@buechler66:

Yes, there is. In fact, with SQL it's simpler than with other tools. For example:

data have ;                                                                                                                             
  input id :$3. from_dt :date. ;                                                                                                        
  format from_dt yymmdd10. ;                                                                                                            
  cards ;                                                                                                                               
f23 01jan2019                                                                                                                           
f23 30jan2019                                                                                                                           
f23 21feb2019                                                                                                                           
f23 25feb2019                                                                                                                           
f23 23feb2019                                                                                                                           
f23 18mar2019                                                                                                                           
f23 16mar2019                                                                                                                           
f23 21mar2019                                                                                                                           
f23 31mar2019                                                                                                                           
f23 21apr2019                                                                                                                           
f23 17apr2019                                                                                                                           
f23 29apr2019                                                                                                                           
f23 13may2019                                                                                                                           
f23 19may2019                                                                                                                           
f23 04may2019                                                                                                                           
f23 01jun2019                                                                                                                           
f23 30jun2019                                                                                                                           
f23 21aug2019                                                                                                                           
f23 25aug2019                                                                                                                           
f23 23aug2019                                                                                                                           
f23 18sep2019                                                                                                                           
f23 16sep2019                                                                                                                           
f23 21sep2019                                                                                                                           
f23 30sep2019                                                                                                                           
f23 21nov2019                                                                                                                           
f23 17nov2019                                                                                                                           
f23 29nov2019                                                                                                                           
f23 13dec2019                                                                                                                           
f23 19dec2019                                                                                                                           
f23 04dec2019                                                                                                                           
s34 01jan2019                                                                                                                           
s34 30jan2019                                                                                                                           
s34 21feb2019                                                                                                                           
s34 25feb2019                                                                                                                           
s34 23feb2019                                                                                                                           
s34 18mar2019                                                                                                                           
s34 16mar2019                                                                                                                           
s34 21mar2019                                                                                                                           
s34 31mar2019                                                                                                                           
s34 21apr2019                                                                                                                           
s34 17apr2019                                                                                                                           
s34 29apr2019                                                                                                                           
s34 13may2019                                                                                                                           
s34 19may2019                                                                                                                           
s34 04may2019                                                                                                                           
s34 01jun2019                                                                                                                           
s34 30jun2019                                                                                                                           
s34 21jul2019                                                                                                                           
s34 25jul2019                                                                                                                           
s34 23jul2019                                                                                                                           
s34 18aug2019                                                                                                                           
s34 16aug2019                                                                                                                           
s34 21aug2019                                                                                                                           
s34 30aug2019                                                                                                                           
s34 21nov2019                                                                                                                           
s34 17nov2019                                                                                                                           
s34 29nov2019                                                                                                                           
s34 13dec2019                                                                                                                           
s34 19dec2019                                                                                                                           
s34 04dec2019                                                                                                                           
;                                                                                                                                       
run ;                                                                                                                                   
                                                                                                                                        
%let bgn_dt='01jan2019'd ;                                                                                                              
%let dt_int = week ;                                                                                                                    
                                                                                                                                        
proc sql ;                                                                                                                              
  create table want as                                                                                                                  
  select id                                                                                                                             
       , 1 + intck ("&dt_int", &bgn_dt, from_dt) as &dt_int._number                                                                     
       , count (*) as count                                                                                                             
  from   have                                                                                                                           
  group  id, &dt_int._number                                                                                                            
  ;                                                                                                                                     
quit ;                                                            

If you want a different date interval, for example, MONTH, just plug it into the macro parameter DT_INT instead of WEEK. Ditto for any other legitimate SAS date interval, such as QUARTER, SEMIYEAR, etc. Try then to see the effect.

Kind regards

Paul D. 

[buechler66]

This code is also awesome. Love how you made it work for any incremental date value!