topic Re: Including missing weeks in COUNT for proc freq in New SAS User

Including missing weeks in COUNT for proc freq

mitrakos — Thu, 05 Nov 2020 19:14:46 GMT

Hello!

I previously asked a similar question in this forum, and was met with a great answer, however this situation is a bit different...

I am trying to include missing weeks in my proc freq analysis with a count of 0, however there is a problem in that the weeks repeat. See below.

ID	date	COUNT
1	20W07	2
1	20W08	1
1	20W09	1
2	20W08	3
2	20W09	1
3	20W05	3
3	20W06	2
3	20W08	1

I am looking at counts between a specific range of dates (20W05 - 20W09). I want the weeks that don't show up for each ID to show up as a count of 0. The complicated part is that the weeks repeat due to the ID's repeating. I suspect it may be some sort of "If and" statement, but I do'n't really know. Using MISSPRINT and MISSING haven't worked. Any ideas?

Re: Including missing weeks in COUNT for proc freq

PaigeMiller — Thu, 05 Nov 2020 19:18:51 GMT

Try PROC FREQ with the SPARSE option. That sounds like exactly what you are asking for.

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=procstat&docsetTarget=procstat_freq_syntax08.htm&locale=en#procstat.freq.freqsparse

Re: Including missing weeks in COUNT for proc freq

Reeza — Thu, 05 Nov 2020 19:20:48 GMT

Did you want to count by ID?
It seems like your code is including ID in the PROC, if you don't want the counts per ID then remove the ID.
If the missing weeks is somewhere in the data set you can use the SPARSE option. If not, you need to use PRELOADFMT or CLASSDATA likely within PROC TABULATE instead of FREQ.

Re: Including missing weeks in COUNT for proc freq

mitrakos — Thu, 05 Nov 2020 19:36:30 GMT

Hello!

Unfortunately I am not sure that worked, I see no 0's in my output. Here is my code:

proc freq data=import;
where date >= '03FEB2020'd and date <='03MAR2020'd;
tables date / sparse out = freq;
format date weeku6.;
by pid;
run;

Re: Including missing weeks in COUNT for proc freq

PaigeMiller — Thu, 05 Nov 2020 19:43:57 GMT

You don't want to put PID in the BY statement. You want

tables pid*date / sparse out = freq;

Re: Including missing weeks in COUNT for proc freq

mitrakos — Thu, 05 Nov 2020 19:47:07 GMT

IT WORKED! Thank you so much!!!