Retain the value

[v307086]

Hi all,

I have a test data with missing values and I want to fill the table by taking previous month's value.

Test data

ID	response	date
1	CR	02Jan2020
2	PR	.
3	NE	04Jan2020
4	SD	.
5	PR	05Jan2020
6	CR	.

data I want to have

ID	response	date
1	CR	02Jan2020
2	PR	02Jan2020
3	NE	04Jan2020
4	SD	02Jan2020
5	PR	05Jan2020

6

CR

05Jan2020

So, if the response NE, then I don't want to retain the NE date but the date before NE. But if the response is not NE, then I wanna retain the previous date. I tried multiple ways, but couldn't figure it out. Please help.

[Astounding]

Not sure if I got the question right, but this looks like a possibility:

data want;
   set have;
   if response ne 'NE' then replacement_date = date;
   if date = . then date = replacement_date;
   drop replacement_date;   retain replacement_date;
run;

[ItsMeAG]

Hi @v307086 ,

Please try the below code :

data test1;
set test;
retain date1;
if not missing(date) then date1= date ;
else date1 =date1;
temp_dt = lag2(date1);
if lag(response) = 'NE' then date1 = temp_dt;
drop temp_dt date ;
rename date1 = date;
run;

I am getting the same output as you requested using this code.

[Kurt_Bremser]

data want;
set have;
retain _date;
if response ne "NE"
then do;
  if date ne .
  then _date = date;
  else date = _date;
end;
drop _date;
run;

[Patrick]

Below should work.

data want(drop=_:);
  set have;
  retain _rdate;
  if response ne 'NE' and not missing(date) then _rdate=date;
  date=coalesce(date,_rdate);
run;

[mkeintz]

And a final minor variation:

data want (drop=_:);
  set have;
  date=coalesce(date,_ret_date);
  retain _ret_date;
  if response^='NE' then _ret_date=date;
run;

Do you literally mean "previous month value", or do you mean the most recent non-missing date value?  I.e., what do you want if you have more than one consecutive missing date?