Hi all,
I would like to fill in missing values using compound interest rates and I would like SAS to continue compounding for a certain number of periods beyond the scope of the current data set. My problem is that I have variables that are missing at different points in the data set. For example,
Have:
date gdp gdp_rate empl empl_rate
1 10 1.004 20 0.998
2 . 1.004 30 0.998
3 . 1.004 40 0.998
4 . 1.004 . 0.998
Want:
date gdp gdp_rate empl empl_rate
1 10 1.004 20 0.998
2 10.04 1.004 30 0.998
3 10.08 1.004 40 0.998
4 10.12 1.004 39.92 0.998
5 10.16 1.004 39.84 0.998
6 10.20 1.004 39.76 0.998
Here is a sample of the do loop I am using. It works fine if I only have one observation in the dataset (to get this I fill in the missing values and keep only the last observation), but I would like the do loop to fill in the missing observations and then keep going until the last iteration. The problem is that the do loop is repeating for each observation...
data want;
set have;
*starting values for temporary vars;
_gdp=gdp;
_emp=empl;
*compute moving estimates by compounding interest;
do i=1 to 27;
_gdp + gdp_rate*_gdp - _gdp; *retains new estimate; *new estimate is actually just the rate times the current value, but this is the only way I knew to retain it;
_empl + empl_rate*_empl - _empl;
new_gdp=lag(_gdp); *lags new estimate so it can be used to replace missing values and fill in future values of gdp;
new_empl=lag(_empl);
if i>1 then
do;
gdp=new_gdp; *sets gdp to new estimate computed using previous period gdp times the growth rate;
empl=new_empl;
end;
output; *outputs observation;
end;
run;
Again, this works fine if there's only one observation in the input data set (e.g. if I just kept observation where date=1). If there are more observations in the input data set as above, the do loop iterates for each observation (i.e. 27 times for date 1, 27 times for date 2, etc.).
Any ideas how to fix this? Or, perhaps you know a much easier way to accomplish this?
This?
This?
Thank you. Thank you. Thank you! This is exactly what I needed. I didn't know about the coalesce function. This makes things easier and much more efficient. Thanks again.
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 25. 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.