Greetings SAS Community!
I'm conducting a study where I intend to calculate direct standardized rates for an outcome of interest. I need to interpolate/forecast the counts, represented as "total_count" in the sample data set, for the years between 2002 and 2007 - hopefully for age-specific and gender stratum.
Software = SAS 9.4: I've used PROC TRANSREG, but I do not think it is designed for interpolation. Other posts discuss using PROC FORECAST, ESM or an ARRAY. However, I'v been unsuccessful using the later.
Thank you,
-Paul
Proc Expand is specialized in time series interpolation. Try:
proc expand data=sample_data factor=5 out=sample_full;
by state descending sex age_group;
id year;
convert total_count;
run;
Are you talking about linear interpolation? If so, then this is a simple formula in a SAS data step.
Hi Paige! Yes, linear interpolation.
Okay, linear interpolation.
So how would you do a linear interpolation? If the population was 2500 in 2002 and 3000 in 2007, then using paper and pencil, or doing it in your head, what is the interpolated value for 2003? What's the formula?
In short, the answer is 100. (y1-y0)/(x1-x0)
Have you looked at PROC EXPAND? It should provide interpolated, regularly-spaced (i.e. yearly in your case) record between the years you already have in your data. It provides a wide variety of interpolation rules, although I suspect you merely want linearly interpolated values of TOTAL_COUNT.
But in your case, you only have two time-points (2002 and 2007) for each state*sex*age_group, so the minimal-learning effort is probably just a data step, as in:
data want (drop=_:);
set 'c:\temp\sample_data.sas7bdat';
by state descending sex age_group;
retain _total2002;
if first.age_group then do;
_total2002=total_count;
output;
end;
else do;
_total2007=total_count;
_change=_total2007-_total2002;
do year=2003 to 2007;
total_count= _total2002 + round(_change*(year-2002)/5);
if year=2007 then total_count=_total2007;
output;
end;
end;
run;
And I belatedly realized I broke up a teaching session. Apologies to @PaigeMiller
Hi mkeintz,
I did not try PROC EXPAND. New to SAS.
For my overall data, my time points are 2002. 2007, 2012, and 2017 with 32 states in each year which equates to 1974 observations.
Thanks,
-Paul
Proc Expand is specialized in time series interpolation. Try:
proc expand data=sample_data factor=5 out=sample_full;
by state descending sex age_group;
id year;
convert total_count;
run;
@psnorrod wrote:
Greetings SAS Community!
I'm conducting a study where I intend to calculate direct standardized rates for an outcome of interest. I need to interpolate/forecast the counts, represented as "total_count" in the sample data set, for the years between 2002 and 2007 - hopefully for age-specific and gender stratum.
Software = SAS 9.4: I've used PROC TRANSREG, but I do not think it is designed for interpolation. Other posts discuss using PROC FORECAST, ESM or an ARRAY. However, I'v been unsuccessful using the later.
Thank you,
-Paul
If discussing US population then the Census Bureau creates annual data sets of population estimates by age, race and gender.
Start at
https://www.census.gov/programs-surveys/popest.html
and look around.
From a certain amount of experience the annual estimates are not simple linear estimates.
Unfortunately, I'm not using Census Bureau data.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.