BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Efthymios
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

4 REPLIES 4
Efthymios
Calcite | Level 5

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

Astounding
PROC Star

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
Calcite | Level 5

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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