topic Re: Calculate Cumulative Totals with Missing Dates in SAS Programming

Calculate Cumulative Totals with Missing Dates

gdaymte — Mon, 27 Apr 2020 15:35:17 GMT

I am looking to calculate cumulative totals by jurisdiction from the first day they had an observation to present (let's assume today is 04/01/2020), but each jurisdiction doesn't always have new observations each day. The cumulative counts are working, but I need to have the missing dates in my table as well, retaining the cumulative total from the last day there were observations.

Original Data

DtCreated	Jurisdiction	CntLocalID
3/24/2020	COUNTYA	1
3/25/2020	COUNTYA	4
3/26/2020	COUNTYA	1
4/1/2020	COUNTYA	1
3/15/2020	COUNTYB	1
3/20/2020	COUNTYB	1
4/1/2020	COUNTYB	4

I have the following code that produces the following table.

/*CUMULATIVE CASE COUNTS BY COUNTY AND DAY*/
DATA CNT_CUMULATIVE; SET CNT_DAILY;
	BY Jurisdiction DtCreated;
	FORMAT DtCreated MMDDYYN8.;
	
	RETAIN CumCntLocalID;
	
	IF FIRST.Jurisdiction THEN CumCntLocalID = CntLocalID;
		ELSE CumCntLocalID = CumCntLocalID + CntLocalID;
	
	DROP CntLocalID;
RUN;

Cumulative Totals

DtCreated	Jurisdiction	CumCntLocalID
3/24/2020	COUNTYA	1
3/25/2020	COUNTYA	5
3/26/2020	COUNTYA	6
4/1/2020	COUNTYA	7
3/15/2020	COUNTYB	1
3/20/2020	COUNTYB	2
4/1/2020	COUNTYB	6

What I need is a table that includes the missing dates for each county, as shown below.

DtCreated	Jurisdiction	CumCntLocalID
3/24/2020	COUNTYA	1
3/25/2020	COUNTYA	5
3/26/2020	COUNTYA	6
3/27/2020	COUNTYA	6
3/28/2020	COUNTYA	6
3/29/2020	COUNTYA	6
3/30/2020	COUNTYA	6
3/31/2020	COUNTYA	6
4/1/2020	COUNTYA	7
3/15/2020	COUNTYB	1
3/16/2020	COUNTYB	1
3/17/2020	COUNTYB	1
3/18/2020	COUNTYB	1
3/19/2020	COUNTYB	1
3/20/2020	COUNTYB	2
3/21/2020	COUNTYB	2
3/22/2020	COUNTYB	2
3/23/2020	COUNTYB	2
3/24/2020	COUNTYB	2
3/25/2020	COUNTYB	2
3/26/2020	COUNTYB	2
3/27/2020	COUNTYB	2
3/28/2020	COUNTYB	2
3/29/2020	COUNTYB	2
3/30/2020	COUNTYB	2
3/31/2020	COUNTYB	2
4/1/2020	COUNTYB	6

Any help would be greatly appreciated.

Re: Calculate Cumulative Totals with Missing Dates

novinosrin — Mon, 27 Apr 2020 16:00:54 GMT

@Reeza and @PGStats taught me this a couple of years ago. This will work if you have SAS ETS licensed



data have;
infile cards expandtabs truncover;
input DtCreated	:mmddyy10. Jurisdiction :$7.	CumCntLocalID;
format DtCreated	mmddyy10.; 
cards;
3/24/2020	COUNTYA	1
3/25/2020	COUNTYA	5
3/26/2020	COUNTYA	6
4/1/2020	COUNTYA	7
3/15/2020	COUNTYB	1
3/20/2020	COUNTYB	2
4/1/2020	COUNTYB	6
;

proc timeseries data=have out=want;
 by Jurisdiction;
 id DtCreated interval=day setmiss=previous;
 var CumCntLocalID;
run;

Jurisdiction	DtCreated	CumCntLocalID
COUNTYA	24Mar2020	1
COUNTYA	25Mar2020	5
COUNTYA	26Mar2020	6
COUNTYA	27Mar2020	6
COUNTYA	28Mar2020	6
COUNTYA	29Mar2020	6
COUNTYA	30Mar2020	6
COUNTYA	31Mar2020	6
COUNTYA	01Apr2020	7
COUNTYB	15Mar2020	1
COUNTYB	16Mar2020	1
COUNTYB	17Mar2020	1
COUNTYB	18Mar2020	1
COUNTYB	19Mar2020	1
COUNTYB	20Mar2020	2
COUNTYB	21Mar2020	2
COUNTYB	22Mar2020	2
COUNTYB	23Mar2020	2
COUNTYB	24Mar2020	2
COUNTYB	25Mar2020	2
COUNTYB	26Mar2020	2
COUNTYB	27Mar2020	2
COUNTYB	28Mar2020	2
COUNTYB	29Mar2020	2
COUNTYB	30Mar2020	2
COUNTYB	31Mar2020	2
COUNTYB	01Apr2020	6

Re: Calculate Cumulative Totals with Missing Dates

Reeza — Mon, 27 Apr 2020 16:07:12 GMT

Just a note that if you add a START and END option to your ID statement it will fill in all the missing dates as well.

id DtCreated interval=day setmiss=previous start = '01Mar2020'd end = '30Apr2020'd;

Re: Calculate Cumulative Totals with Missing Dates

gdaymte — Mon, 27 Apr 2020 16:21:20 GMT

You have certainly made my day. Worked like a charm. It looks like our Desktop SAS is licensed for SAS/ETS, but not our SAS Studio account.