Hello,
I would like to have a value repeated when the next line has a '.' and then when the next line has a value to start using that value to repeat. I've been trying a "If x then do y" sort of thing and I cannot get it right.
My data looks like this
id | Type | date 1 | date 2 |
1 | a | 1/1/2016 | . |
1 | b | 1/4/2016 | 1/4/2016 |
1 | a | 5/4/2016 | . |
1 | a | 6/7/2017 | . |
1 | b | 6/8/2017 | 6/8/2017 |
1 | a | 6/10/2017 | . |
2 | a | 3/5/2012 | . |
2 | b | 4/5/2012 | 4/5/2012 |
2 | b | 4/12/2012 | 4/12/2012 |
2 | a | 4/15/2012 | . |
2 | b | 5/7/2014 | 5/7/2014 |
And I want it to look like this
id | Type | date 1 | date 2 |
1 | a | 1/1/2016 | . |
1 | b | 1/4/2016 | 1/4/2016 |
1 | a | 5/4/2016 | 1/4/2016 |
1 | a | 6/7/2017 | 1/4/2016 |
1 | b | 6/8/2017 | 6/8/2017 |
1 | a | 6/10/2017 | 6/8/2017 |
2 | a | 3/5/2012 | . |
2 | b | 4/5/2012 | 4/5/2012 |
2 | b | 4/12/2012 | 4/12/2012 |
2 | a | 4/15/2012 | 4/12/2012 |
2 | b | 5/7/2014 | 5/7/2014 |
edit to show the first id 2 should have a '.' for date 2 not the date 2 from id 1.
Thank you!
may be something like this
proc sort data =want; by id ; run; data have; set want; by id; retain newdate; if ((first.id or last.id) or not (first.id or last.id )) and date2 ne . then newdate=date2; date2=newdate; format newdate date9.; run;
Perhaps simpler:
data want;
set have;
by id;
retain replacement_date;
if first.id or date2 > . then replacement_date = date2;
drop date2;
rename replacement_date = date2;
run;
Another possibility that may be simpler and does not rely on dropping variables:
data new;
set old;
length repdate 8;
retain repdate;
if _n_ = 1 then do;
if date2 ne . then repdate = date2;
end;
else do;
if date2 = . then date2 = repdate;
else repdate = date2;
end;
run;
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
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.
Ready to level-up your skills? Choose your own adventure.