I have a scenario where I have missing data that I would like to fill in with the previous (non-missing) observation. So, for example my data looks like so:
Obs FFY2012
1 0.0
2 0.2
3 .
4 0.3
5 0.5
6 0.8
....
And I want to fill in Obs #3 with 0.2 (i.e., the value from Obs #2).
So I wrote up this code and it works well...
if ffy2012 = . and open_cit_lag_c = 0 then ffyear2012 = 0; if ffy2012 ne . then ffyear2012 = ffy2012; retain ffyear2012;
(Open_cit_lag_c = 0 is the first observation, and sometimes this value is missing as well. Just FYI...)
However, I have 5 such variables that need to be fixed, so I tried to write this up as an array, like so:
array FFY{5} FFY2011 FFY2012 FFY2013 FFY2014 FFY2015; array FFYEAR{5} FFYEAR2011 FFYEAR2012 FFYEAR2013 FFYEAR2014 FFYEAR2015; do i = 1 to 5; if ffy{i} = . and open_cit_lag_c = 0 then ffyear{i} = 0; if ffy{i} ne . then ffyear{i} = ffy{i}; retain ffyear{i}; end;
And it errored out at the retain statement. It's early in the morning, so it's possible that I'm missing something small. But I'm wondering if my use of a retain statement within the array do loop is causing some sort of crossing of streams? Anywho, any thoughts would help.
Don't put the RETAIN in the loop, It is not an executable statement.
array FFY FFY2011 FFY2012 FFY2013 FFY2014 FFY2015;
array FFYEAR FFYEAR2011 FFYEAR2012 FFYEAR2013 FFYEAR2014 FFYEAR2015;
retain FFYEAR2011 FFYEAR2012 FFYEAR2013 FFYEAR2014 FFYEAR2015;
do i = 1 to dim(FFY);
if ffy{i} = . and open_cit_lag_c = 0 then ffyear{i} = 0;
if ffy{i} ne . then ffyear{i} = ffy{i};
end;
Note that if you assign initial values in the ARRAY statement then SAS will automatically retain the values.
array FFYEAR FFYEAR2011 FFYEAR2012 FFYEAR2013 FFYEAR2014 FFYEAR2015 (5*.);
Don't put the RETAIN in the loop, It is not an executable statement.
array FFY FFY2011 FFY2012 FFY2013 FFY2014 FFY2015;
array FFYEAR FFYEAR2011 FFYEAR2012 FFYEAR2013 FFYEAR2014 FFYEAR2015;
retain FFYEAR2011 FFYEAR2012 FFYEAR2013 FFYEAR2014 FFYEAR2015;
do i = 1 to dim(FFY);
if ffy{i} = . and open_cit_lag_c = 0 then ffyear{i} = 0;
if ffy{i} ne . then ffyear{i} = ffy{i};
end;
Note that if you assign initial values in the ARRAY statement then SAS will automatically retain the values.
array FFYEAR FFYEAR2011 FFYEAR2012 FFYEAR2013 FFYEAR2014 FFYEAR2015 (5*.);
Slick! That fixed it. Thanks!
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.