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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.