## Interpolation for Intercensal Years

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

1 ACCEPTED SOLUTION

Accepted Solutions  PGStats
Opal | Level 21

## Re: Interpolation for Intercensal Years

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;``````
PG
9 REPLIES 9

## Re: Interpolation for Intercensal Years

Are you talking about linear interpolation? If so, then this is a simple formula in a SAS data step.

--
Paige Miller

## Re: Interpolation for Intercensal Years

Hi Paige! Yes, linear interpolation.

## Re: Interpolation for Intercensal Years

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?

--
Paige Miller

## Re: Interpolation for Intercensal Years

In short, the answer is 100. (y1-y0)/(x1-x0)

## Re: Interpolation for Intercensal Years

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

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

## Re: Interpolation for Intercensal Years

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  PGStats
Opal | Level 21

## Re: Interpolation for Intercensal Years

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;``````
PG

## Re: Interpolation for Intercensal Years

@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.

## Re: Interpolation for Intercensal Years

Unfortunately, I'm not using Census Bureau data.

Discussion stats
• 9 replies
• 613 views
• 4 likes
• 5 in conversation