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

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

gzr2mz39 — Mon, 25 Aug 2014 20:56:28 GMT

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.

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

ballardw — Mon, 25 Aug 2014 21:20:38 GMT

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;

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

Peter_C — Mon, 25 Aug 2014 21:23:01 GMT

see the COMPLETETYPES option of PROC MEANS

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

stat_sas — Mon, 25 Aug 2014 21:41:20 GMT

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;

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

gzr2mz39 — Mon, 25 Aug 2014 22:13:43 GMT

This works great. Thank you.