Including missing weeks in COUNT for proc freq

[mitrakos]

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? 

[PaigeMiller · Accepted solution]

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=pro...

[mitrakos]

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;

[PaigeMiller]

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

tables pid*date / sparse out = freq;

[mitrakos]

IT WORKED! Thank you so much!!!

[Reeza]

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.