Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- SAS Procedures
- /
- Interpolation for Intercensal Years

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 11-07-2019 02:25 PM
(612 views)

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

**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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

--

Paige Miller

Paige Miller

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hi Paige! Yes, linear interpolation.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

Paige Miller

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

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

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

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

**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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Unfortunately, I'm not using Census Bureau data.

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

**If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. **

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.