Assign Zero Value to Age_Groups for Rate Analysis

[psnorrod]

Hello All!

Learning SAS user back again.

I'm working with ecological data, and (thanks to the SAS community!!!) successfully used code that counts the number of events across three variables (year, sex, age_group). The data is all events, so there are no "zeros" in the original data set. Now, I need to either generate zeros for the missing groups that do not have events or merge a data set that has "zeros" using a conditional statement. My final intent is to obtain rates using PROC GENMOD employing a nb or zero-inflated model. All that said, I'm uncertain as to how to generate or create the zeros in the sql data set for my final analysis.

Code to create the count:

proc sql;
create table work.sql as
select year, sex, age_group, count(*) as count
from one
group by year, sex, age_group
;
quit;

Variables: year, site, sex=1 or 2, age_group=1, 2, or 3

Sample sql data set:

year   site  sex  age_group  counts

2003   66      2         3                  11

2003   66      2         3                   21

2003   66      2          2                  17

2003   66     1           1                   3

2003   66      1          2                   1

As you likely see, there are no counts or "zeros" for age_groups 1 and 2 where sex=2 and so on.

Thank you for all of your help!

[ed_sas_member · Accepted solution]

Hi @psnorrod 

You can use the sparse option in PROC FREQ

proc freq data=one noprint;
	tables year * sex * age_group / out=sql (drop=percent) sparse;
	weight counts;
quit;

NB: I believe that the variable COUNTS is the weight variable but not sure. Please feel free to remove it.

[psnorrod]

working on running now.

[ballardw]

One approach might be the Proc Summary with the COMPLETETYPES options using your SAMPLE1 data set from your other question:

proc summary data=sample1 completetypes nway;
 class year sex age_group;
 output out=work.summary (drop=_type_ rename=(_freq_=count));
run;

which will have ALL of the class variable levels with counts of 0 for the ones that did not actually occur. So you will sex 1 & 2 with age_group 1, 2 & 3 for each year.

[psnorrod]

Thank you all so very much!!!

I was able to get the solution posted by @ed_sas_member to work. And the code posted by @ballardw also worked!

So grateful for the help from both of you and the Programming community! I was soooo frustrated over the weekend and yesterday.

Again, thank you @ed_sas_member and @ballardw 

[ed_sas_member]

My pleasure @psnorrod