DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Regular Contributor
Posts: 196
Accepted Solution

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

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.


Accepted Solutions
Solution
‎08-25-2014 05:20 PM
Super User
Posts: 10,500

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

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;

View solution in original post


All Replies
Solution
‎08-25-2014 05:20 PM
Super User
Posts: 10,500

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

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;

Regular Contributor
Posts: 196

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

This works great. Thank you.

Valued Guide
Posts: 2,175

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

see the COMPLETETYPES option of PROC MEANS

Trusted Advisor
Posts: 1,204

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

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 230 views
  • 0 likes
  • 4 in conversation