Trying to fill missing values where vars missing at different points, how to avoid do loop repeating for ea. obs.?

pecon1 — Tue, 21 Jan 2014 21:06:14 GMT

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?

Re: Trying to fill missing values where vars missing at different points, how to avoid do loop repeating for ea. obs.?

data_null__ — Tue, 21 Jan 2014 22:45:41 GMT

This?

data gdp;
   id = 1; 
   input date gdp gdp_rate  empl empl_rate;
   cards; 
1  10  1.004  20  0.998
2  .   1.004  30  0.998
3  .   1.004  40  0.998
4  .   1.004  .   0.998
;;;;
   run; 
proc print; 
   run; 
data gdp2;
   do until(last.id);
      update gdp(obs=0) gdp(keep=id date gdp_rate empl_rate);
      by id;
      set gdp(keep=gdp empl);
      gdp2  = coalesce(gdp,gdp2*gdp_rate);
      empl2 = coalesce(empl,empl2*empl_rate);
      output; 
      end; 
   do date=date+1 to 27; 
      gdp2  = coalesce(gdp,gdp2*gdp_rate);
      empl2 = coalesce(empl,empl2*empl_rate);
      output; 
      end;      
   run; 
proc print; 
   run;

Re: Trying to fill missing values where vars missing at different points, how to avoid do loop repeating for ea. obs.?

pecon1 — Wed, 22 Jan 2014 00:10:16 GMT

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.

topic Re: Trying to fill missing values where vars missing at different points, how to avoid do loop repeating for ea. obs.? in SAS Programming

Trying to fill missing values where vars missing at different points, how to avoid do loop repeating for ea. obs.?

Re: Trying to fill missing values where vars missing at different points, how to avoid do loop repeating for ea. obs.?

Re: Trying to fill missing values where vars missing at different points, how to avoid do loop repeating for ea. obs.?