DATA Step, Macro, Functions and more

re: Add Row Data Step

Reply
Regular Contributor
Posts: 229

re: Add Row Data Step

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

 

Super User
Posts: 19,817

Re: re: Add Row Data Step

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;
Super User
Posts: 10,035

Re: re: Add Row Data Step

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;
       
Ask a Question
Discussion stats
  • 2 replies
  • 223 views
  • 2 likes
  • 3 in conversation