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

[Efthymios]

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

[Kurt_Bremser]

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;

[Efthymios]

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

[Astounding · Accepted solution]

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.

[Efthymios]

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