DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

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

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


Accepted Solutions
Solution
‎10-22-2017 03:15 PM
Super User
Posts: 6,934

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

Posted in reply to Efthymios

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.

View solution in original post


All Replies
Super User
Posts: 10,574

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

Posted in reply to Efthymios

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
New Contributor
Posts: 4

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

Posted in reply to KurtBremser

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

Solution
‎10-22-2017 03:15 PM
Super User
Posts: 6,934

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

Posted in reply to Efthymios

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.

New Contributor
Posts: 4

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

Posted in reply to Astounding

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 609 views
  • 0 likes
  • 3 in conversation