Quickest way to fill in missing dates in a sequence? SAS

Question

Say you download data for stocks or bonds, you have the stock price or yield for every trading day. So you have two variables, stock price (or yield if bond) and date. What is the quickest way to add weekends and holidays to the dates variable while using the previous open day as the values for those missing days?

For example, if it were July 1, 2022 there would be a stock price, lets say $100, corresponding to that date, but during the long weekend (4th of July) there are no observations in the data with the date being July 2nd through 4th. How do you add those dates with the stock price equaling $100 until the next trading day, July 5th?

I used a do loop to create the dates then merged and retain, but I feel like theres got to be a quicker method

Answer 1

You could just add an OUTPUT statement in a DO loop. The tricky part is getting the next date. Here is a method using a second SET statement that is offset by one observation.

data want;
  set have ;
  by date;
  set have(firstobs=2 keep=date rename=(date=next_date)) have(obs=0 drop=_all_);
  next_date = coalesce(next_date,date);
  do date=date to next_date;
    output;
  end;
run;

But your real data probably has multiple stocks. So add some BY group processing.

data want;
  set have ;
  by stock date;
  set have(firstobs=2 keep=date rename=(date=next_date)) have(obs=0 drop=_all_);
  if last.stock the next_date=date;
  do date=date to next_date;
    output;
  end;
run;