DATA Step, Macro, Functions and more

How to repeat value until the next value and then repeat that

Reply
Occasional Contributor
Posts: 13

How to repeat value until the next value and then repeat that

[ Edited ]

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!

PROC Star
Posts: 251

Re: How to repeat value until the next value and then repeat that

[ Edited ]

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;
Super User
Posts: 5,071

Re: How to repeat value until the next value and then repeat that

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;

Contributor
Posts: 25

Re: How to repeat value until the next value and then repeat that

[ Edited ]

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;

Ask a Question
Discussion stats
  • 3 replies
  • 108 views
  • 1 like
  • 4 in conversation