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;
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.