Insert rows with count=0 for categorical variable combinations that don't exist in data

[gzr2mz39]

I have three fields: Requirement, Level, and Count.

Requirement contains observations like Air, Biological, Cultural, etc.

Level only contains: 1, 2, 3, OB.

Count (>0) is the number of observations by Requirement and Level.

I need to create rows with Count=0 for Requirements/Levels that don't contain any observations.

For example, in the situation below I need to insert a row for Requirement=Air, Level=3, and Count=0.

I don't think PROC SQL Insert Into is an option since there are many rows that need to be added.

Requirement Level Count

Air    1    64

Air    2    20

Air    OB     2

Biological    1    107

Biological    2    104

Biological    3    4

Biological    OB    194

Does anyone have any suggestions?

Thank you.

[ballardw · Accepted solution]

Some thing like this perhaps:

proc sql;

     create table temp1 as

     select distinct requirement

     from yourdatasetnamehere;

quit;

data temp2;

     set temp1;

     do level = '1','2','3','OB';

          output;

     end;

run;

proc sql;

     create table want as

     select a.requirement,a.level,

               case

                    when not missing(b.count) then b.count

                    else 0

               end as count

     from temp2 as a left join yourdatasetnamehere as b on

          a.requirement=b.requirement and a.level=b.level;

quit;

[gzr2mz39]

This works great. Thank you.

[Peter_C]

see the COMPLETETYPES option of PROC MEANS

[stat_sas]

data have;
input Requirement $ 1-11 Level $ Count;
datalines;
Air        1    64
Air        2    20
Air        OB   2
Biological 1    107
Biological 2    104
Biological 3    4
Biological OB   194
;

data have1;
input Requirement $ 1-11 Level $;
datalines;
Air        1
Air        2
Air        3
Air        OB
Biological 1
Biological 2
Biological 3
Biological OB
;
proc sql;
select a.level,a.Requirement,coalesce(b.Count,0) as count from have1 a
left join have b
on a.Requirement=b.Requirement
and a.level=b.level
order by Requirement,level;
quit;