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.
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;
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;
This works great. Thank you.
see the COMPLETETYPES option of PROC MEANS
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.