re: Add Row Data Step

[twildone]

Hi....I have a dataset that I am trying to add a row for each region if Level =1 is missing. The objective is to have 4 records for each Region and Levels 2, 3 and 4 will always appear as these Levels are manditory whereas Level=1 is optional. If Level=1 is missing for the Region, then a new recorded is added so that Levels 1, 2 ,3 and 4 appear where all fields appear blank except for the field Level which will have a "1" and the Region.

The code I have tried is:

Data New;

Set New;

By Region;

If Level ^=1 then do:

Region =Region;

Level=1;

end;

output;

run;

Have:

Region	Level	Cost
South	2	132
South	3	143
South	4	100
North	1	200
North	2	210
North	3	187
North	4	178

Want:

Region	Level	Cost
South	1
South	2	132
South	3	143
South	4	100
North	1	200
North	2	210
North	3	187
North	4	178

[Reeza]

If you have all the levels at some point in your data set you can use the sparse option in proc freq. 

proc freq data=have;
table region*level/sparse out=want;
weight cost;
run;

[Ksharp]

It is about Cartesian Product of SQL.

data have;
infile cards expandtabs truncover;
input Region $	Level	Cost;
cards;
South	2	132
South	3	143
South	4	100
North	1	200
North	2	210
North	3	187
North	4	178
;
run;

proc sql;
select a.*,b.cost
 from (
  select * 
   from (select distinct Region from have),
        (select distinct Level  from have)
       ) 
       as a left join have as b 
        on a.Region=b.Region  and a.Level=b.Level  ;
quit;