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 |
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;
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.