topic Re: Interpolation for Intercensal Years in SAS Procedures

Interpolation for Intercensal Years

psnorrod — Thu, 07 Nov 2019 19:26:01 GMT

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

Re: Interpolation for Intercensal Years

PaigeMiller — Thu, 07 Nov 2019 19:32:31 GMT

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

Re: Interpolation for Intercensal Years

psnorrod — Thu, 07 Nov 2019 19:36:52 GMT

Hi Paige! Yes, linear interpolation.

Re: Interpolation for Intercensal Years

PaigeMiller — Thu, 07 Nov 2019 19:38:40 GMT

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?

Re: Interpolation for Intercensal Years

mkeintz — Thu, 07 Nov 2019 19:54:27 GMT

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

Re: Interpolation for Intercensal Years

psnorrod — Fri, 08 Nov 2019 22:23:43 GMT

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

Re: Interpolation for Intercensal Years

PGStats — Thu, 07 Nov 2019 20:00:38 GMT

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;

Re: Interpolation for Intercensal Years

psnorrod — Thu, 07 Nov 2019 20:01:48 GMT

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

Re: Interpolation for Intercensal Years

ballardw — Thu, 07 Nov 2019 20:09:07 GMT

@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

psnorrod — Thu, 07 Nov 2019 20:12:01 GMT

Unfortunately, I'm not using Census Bureau data.