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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

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

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?


Accepted Solutions
Solution
‎01-21-2014 05:45 PM
Respected Advisor
Posts: 3,777

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

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;

View solution in original post


All Replies
Solution
‎01-21-2014 05:45 PM
Respected Advisor
Posts: 3,777

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

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;
Occasional Contributor
Posts: 16

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

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 277 views
  • 0 likes
  • 2 in conversation