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.
@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 |
@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 |
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.