topic Re: How to repeat value until the next value and then repeat that in SAS Programming

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

weweaw — Thu, 15 Jun 2017 14:29:38 GMT

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!

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

kiranv_ — Thu, 15 Jun 2017 13:41:57 GMT

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;

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

Astounding — Thu, 15 Jun 2017 13:56:17 GMT

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;

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

JohnSAScom — Thu, 15 Jun 2017 14:47:55 GMT

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;