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

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

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

View solution in original post

9 REPLIES 9
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
psnorrod
Obsidian | Level 7

Hi Paige! Yes, linear interpolation. 

PaigeMiller
Diamond | Level 26

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
psnorrod
Obsidian | Level 7

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

mkeintz
PROC Star

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

--------------------------
psnorrod
Obsidian | Level 7

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

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
ballardw
Super User

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

psnorrod
Obsidian | Level 7

Unfortunately, I'm not using Census Bureau data. 

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

What is Bayesian Analysis?

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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