topic Retain the value in SAS Programming

Retain the value

v307086 — Fri, 31 May 2024 16:03:59 GMT

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

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.

Re: Retain the value

Astounding — Fri, 31 May 2024 16:16:41 GMT

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;

Re: Retain the value

ItsMeAG — Fri, 31 May 2024 16:52:59 GMT

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.

Re: Retain the value

Kurt_Bremser — Fri, 31 May 2024 22:39:43 GMT

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;

Re: Retain the value

Patrick — Sat, 01 Jun 2024 03:56:21 GMT

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;

Re: Retain the value

mkeintz — Sun, 02 Jun 2024 20:17:38 GMT

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?