Here is an example of the data where there is a variable
that holds a date, and the corresponding amount.
Date_var Amount
05/15/2017 150
05/16/2017 175
05/19/2017 190
05/20/2017 165
As you can see, the weekend values are missing. How can I
create additional records to this dataset that fill in the
missing weekend dates, where the Amount will be the same as
the previous business
day that was populated? The data set should look like this when complete.
Date_var Amount
05/15/2017 150
05/16/2017 175
05/17/2017 175
05/18/2017 175
05/19/2017 190
05/20/2017 165
Check out PROC TIMESERIES.
What if the missing date was not a weekend date? Should you leave out that record or still add this date with the amount of the previous record to the dataset?
If the date was missing, would always want to fill with the previous day amount. Valid business days will always have a date record populated, even if the Amount variable is $0. Only Bank holidays and weekends will be missing. I need something that will fill in the missing dates, and carry over the amount from the previous business day. Thanks.
You can use next code which checks absent dates are they realy weekend or not
data test;
infile cards;
input date_var mmddyy10. amount;
format date_var date9.;
cards;
05/15/2017 150
05/16/2017 175
05/19/2017 190
05/20/2017 165
;
run;
proc means data=test;
class;
var date_var;
output out=min_max min=min_dt max=max_dt;
run;
data all_days;
set min_max;
keep date_var weekday;
do date_var = min_dt to max_dt;
weekday = weekday(date_var);
output;
end;
run;
data want;
merge all_days test;
by date_var;
retain amount lag_qty;
if not missing(amount) then do;
lag_qty = amount;
output;
end;
else do;
if weekday in (6, 7) then do; /* weekend days */
amount = lag_qty;
output;
end;
else put '>>> ' Date_var= date9. weekday=;
end;
run;
Thanks so much. Your second "else do" worked perfectly. Appreciate the help!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: