05-04-2016 06:05 PM
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:
If Level ^=1 then do:
05-04-2016 09:21 PM
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;