topic Re: Assign Zero Value to Age_Groups for Rate Analysis in SAS Programming

Assign Zero Value to Age_Groups for Rate Analysis

psnorrod — Tue, 17 Dec 2019 18:08:42 GMT

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!

Re: Assign Zero Value to Age_Groups for Rate Analysis

ed_sas_member — Tue, 17 Dec 2019 18:20:38 GMT

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.

Re: Assign Zero Value to Age_Groups for Rate Analysis

psnorrod — Tue, 17 Dec 2019 18:28:02 GMT

working on running now.

Re: Assign Zero Value to Age_Groups for Rate Analysis

ballardw — Tue, 17 Dec 2019 18:30:04 GMT

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.

Re: Assign Zero Value to Age_Groups for Rate Analysis

psnorrod — Tue, 17 Dec 2019 18:37:22 GMT

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

Re: Assign Zero Value to Age_Groups for Rate Analysis

ed_sas_member — Tue, 17 Dec 2019 18:38:08 GMT

My pleasure @psnorrod