topic Re: How to make cells with zero counts show up with proc freq statements in SAS Programming

How to make cells with zero counts show up with proc freq statements

steph_ — Mon, 15 Apr 2024 13:08:24 GMT

Hello!

I am trying to look at the number of asthma counts by zip code, but when I run the proc freq statement, the zip codes with zero counts of asthma do show up in the results viewer. How can I fix this? Example code below. I have already tried putting 'missing' at the end of the tables step.

proc freq data=work.datarequest;
tables zipcode*ASTHMA / norow nocol nopercent;
where ASTHMA=1;
run;

Re: How to make cells with zero counts show up with proc freq statements

Tom — Mon, 15 Apr 2024 13:12:01 GMT

Don't use the WHERE statement. That will prevent PROC FREQ from ever seeing the zipcodes that never have ASTHMA=1.

Re: How to make cells with zero counts show up with proc freq statements

steph_ — Mon, 15 Apr 2024 13:13:39 GMT

Ideally, I wanted to keep the where statement so I do not have to format the data as much. Since I am only interested in Asthma cases.

Re: How to make cells with zero counts show up with proc freq statements

Tom — Mon, 15 Apr 2024 13:42:59 GMT

I cannot visualize what you are trying to do. Can you provide input data and expected results. Just enough data so it demonstrates the issue.

Probably PROC FREQ is not the right tool. For example PROC SUMMARY (aka MEANS) has methods to allow you to provide information on the complete list of possible values.

Re: How to make cells with zero counts show up with proc freq statements

Rick_SAS — Mon, 15 Apr 2024 17:44:14 GMT

Here is some sample data. For these data, the PROC FREQ step shows only the frequencies for ASTHMA=1. It does not show any cells for missing values.

data Have;
infile datalines missover;
input zipcode ASTHMA;
datalines;
12345 1
12345 1
12345 0
12345 
52345 1
52345 1
52345 0
52345 
52345 1
52345 
65432 0
65432 
;

proc freq data=Have;
tables zipcode*ASTHMA / norow nocol nopercent;
where ASTHMA=1;
run;

If this does not answer your question, please modify the input data to mimic the data that you have.

Re: How to make cells with zero counts show up with proc freq statements

Ksharp — Tue, 16 Apr 2024 02:53:54 GMT

First way is trying SPARSE option:
proc freq data=work.datarequest;
tables zipcode*ASTHMA / norow nocol nopercent sparse ;
run;

Second way is using WEIGHT statement @Rick_SAS mentioned this skill in one of his blog. Post your sample data that would be better to demonstrate how to use WEIGHT .