BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
pecon1
Fluorite | Level 6

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?

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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

2 REPLIES 2
data_null__
Jade | Level 19

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;
pecon1
Fluorite | Level 6

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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