topic Re: Counting occurrence of 14 or more consecutive blank cells in a column in SAS Programming

Counting occurrence of 14 or more consecutive blank cells in a column

Barkat — Mon, 22 Nov 2021 16:50:11 GMT

The hypothetical table shows daily number of COVID death in four States from December 2020 to November 2021. There are some death free days or periods. Is there any way to count the number of death free periods in each State when there was no death for at least 14 days in a row (consecutive 14 or more days). Also finding the start and end date of those death free periods.

Date	CA	NY	PA	FL
12/1/2020	2	8	3
12/2/2020	6	6	3
12/3/2020	1	9
12/4/2020			5
12/5/2020		2
12/6/2020		1	4
12/7/2020			2
12/8/2020
12/9/2020		3
12/10/2020
12/11/2020			3
12/12/2020			2
12/13/2020			1
12/14/2020			1
12/15/2020
12/16/2020				5
12/17/2020
12/18/2020				4
12/19/2020	1
12/20/2020
12/21/2020	6
12/22/2020
12/23/2020
12/24/2020
12/25/2020	4
12/26/2020
12/27/2020				3
12/28/2020		4
12/29/2020
12/30/2020		1
12/31/2020	2
1/1/2021			4
-
-
-
11/18/2021
11/19/2021		2
11/20/2021	1		2	1

Thanks,

Re: Counting occurrence of 14 or more consecutive blank cells in a column

PaigeMiller — Mon, 22 Nov 2021 17:18:11 GMT

Blanks should be zero.

Then, if you have PROC EXPAND in SAS/ETS, you want a moving sum of length 14. If that moving sum is zero, that's what you are looking for.

Re: Counting occurrence of 14 or more consecutive blank cells in a column

PeterClemmensen — Mon, 22 Nov 2021 18:43:43 GMT

So what does your desired result look like?

Re: Counting occurrence of 14 or more consecutive blank cells in a column

KevinScott — Mon, 22 Nov 2021 18:44:13 GMT

Check out proc timeid(SAS/ETS) it will find and calculate the length of gaps in your time series:

https://communities.sas.com/t5/SAS-Communities-Library/Tips-Identifying-and-Locating-Missing-Values-and-Gaps-in-Time/ta-p/223429

Re: Counting occurrence of 14 or more consecutive blank cells in a column

Astounding — Mon, 22 Nov 2021 21:37:24 GMT

Hmmm. You talk about the objective, but leave it wide open as to what the result should look like. So here is one way to display the result.

data want;
set have;
array counts {4} _temporary_ (0 0 0 0);
array states {4} CA NY PA FL;
do _n_=1 to 4;
   if states{_n_} then counts{_n_} = 0;
   else counts{_n_} = counts{_n_} + 1;
   if counts{_n_} >= 14 then states{_n_} = .F;
end;
run;

This will actually change the data values for CA, NY, PA, and FL. Any time there are at least 14 periods without a death, the value will change to .F (a special missing value). You can inspect the resulting data to look for any sets of .F values. So the output will have the same variables you started with, with .F for the counts when there are at least 14 consecutive no-death days.

Of course, if you have a different outcome in mind, you can always provide details about what it should look like.