topic Re: Replacing missing values with previous value in groups-How to reset retain function in SAS Programming

Replacing missing values with previous value in groups-How to reset retain function

Efthymios — Sun, 22 Oct 2017 17:50:04 GMT

Hi all,

I have a question regarding replacing missing values with previous value. I know that this topic has been covered but it seems I can't get it right. My dataset consists of Bid and Ask quotes and is sorted by date and time. I want to replace the missing quotes with the previous available but doing so separately for each date. For example I don't want the quote from the previous date to carry into the next. I tried the following code:

data test2;
set test1;
by Date;
retain Bidint;
if first.date then do;
if Bid>. then Bidint=Bid;
end;

But what it does it retaining only the first observation of each date. What I really want is to reset retain function at the beginning of each date. Thank you in advance

Re: Replacing missing values with previous value in groups-How to reset retain function

Kurt_Bremser — Sun, 22 Oct 2017 17:59:26 GMT

Slightly change the logic:

data test2;
set test1;
by Date;
retain Bidint;
if first.date or Bid ne . then Bidint = Bid;
if not first.date and bid = . then bid = bidint;
run;

Re: Replacing missing values with previous value in groups-How to reset retain function

Astounding — Sun, 22 Oct 2017 18:51:08 GMT

It's not 100% clear where you are heading here. Which variable is supposed to hold which values? And is there only one CUSIP per data set? Here's one possibility:

data test2;

set test1;

by date;

retain BidInt;

if first.date or bid > . then BidInt = bid;

run;

For this code, BIDINT will hold the most recent nonmissing bid, with missing BID values replaced by the most recent BID values.

Of course if you have multiple CUSIPs, you will need to add a variable to the BY statement.

Re: Replacing missing values with previous value in groups-How to reset retain function

Efthymios — Sun, 22 Oct 2017 19:15:42 GMT

Thanks for the reply Astounding. I have separated the stocks in different tables so no need for the CUSIP.

Re: Replacing missing values with previous value in groups-How to reset retain function

Efthymios — Sun, 22 Oct 2017 19:16:59 GMT

Thanks KurtBremser. Tried also your code and it works. Much appreciate the quick response