topic Re: Aging a Given Population in SAS by Month in SAS Programming

Aging a Given Population in SAS by Month

VGutz — Mon, 26 Feb 2018 15:17:36 GMT

I'm looking for a way top age a given population in SAS based on current age. The population should be aged on a monthly basis, based on the month's age distribution.

My current dataset includes the most recent completed month of population percentages by age and gender. Perc_0 is the current percentage of total population by age/gender. I would like to age the population based on these given formulas:

i = current age

Youngest age group: Next month's percentage = (i)th group percentage * (11/12)

All other age groups: Next month's percentage = (i)th group percentage * (11/12) + (i-1)th group percentage * (1/12)

I've attached an excel sheet displaying the current data set (highlighted in grey) and an example of a 10 month projection.

I've tried using arrays to loop through the data, but I can't figure out how to build it off of each new month of projected data; because I have to project out 24 month's worth of data, I don't want to have 24 steps copied out if I can just put it into one array or do loop.

I appreciate any help. Thank you.

Re: Aging a Given Population in SAS by Month

Reeza — Mon, 26 Feb 2018 16:05:46 GMT

Please post what you have so far.

@VGutz wrote:

I'm looking for a way top age a given population in SAS based on current age. The population should be aged on a monthly basis, based on the month's age distribution.

My current dataset includes the most recent completed month of population percentages by age and gender. Perc_0 is the current percentage of total population by age/gender. I would like to age the population based on these given formulas:

i = current age

Youngest age group: Next month's percentage = (i)th group percentage * (11/12)

All other age groups: Next month's percentage = (i)th group percentage * (11/12) + (i-1)th group percentage * (1/12)

I've attached an excel sheet displaying the current data set (highlighted in grey) and an example of a 10 month projection.

I've tried using arrays to loop through the data, but I can't figure out how to build it off of each new month of projected data; because I have to project out 24 month's worth of data, I don't want to have 24 steps copied out if I can just put it into one array or do loop.

I appreciate any help. Thank you.

Re: Aging a Given Population in SAS by Month

VGutz — Wed, 28 Feb 2018 19:40:09 GMT

So far I have this code, but I need to figure out a way to loop in for 24 months.

data prev_month_0;

set month_0;

format prev_perc_0 BEST12.;

age = age + 1;

prev_perc_0 = perc_0;

drop perc_0;

run;

proc sql;

create table join_month_0 as

select coalesce(t1.year,t2.year) as year,

coalesce(t1.month,t2.month) as month,

coalesce(t1.quarter,t2.quarter) as quarter,

coalesce(t1.sex_cd,t2.sex_cd) as sex_cd,

coalesce(t1.age,t2.age) as age,

coalesce(t1.perc_0, 0) as perc_0,

coalesce(t2.prev_perc_0, 0) as prev_perc_0

from month_0 as t1

full outer join prev_month_0 as t2

on t1.year = t2.year

and t1.month = t2.month

and t1.age = t2.age

and t1.sex_cd = t2.sex_cd;

quit;

data month_1;

set join_month_0;

format perc_1 BEST12.;

perc_1 = (11/12) * perc_0 + (1/12) * prev_perc_0;

drop prev_perc_0;

run;

Re: Aging a Given Population in SAS by Month

mkeintz — Thu, 01 Mar 2018 05:15:17 GMT

This is a problem that benefits from a solution that uses record order, which is not a strength of PROC SQL:

data have;
  year=2017; month=12;
  do sex='M','F';
    do age=20 to 29;
      input perc_0 @;
      output;
    end;
  end;
  format perc_0 percent7.1;
datalines;
.11 .13 .11 .07 .03 0 0 0 0 0
.12 .15 .13 .1  .05 0 0 0 0 0
run;

data want (drop=_:);
  set have;
  by descending sex  age;
  array prc {0:10} perc_0-perc_10;
  retain perc_1-perc_10;
  format perc_0-perc_10 percent7.1;

  do _m=1 to 10;
    if first.sex then  prc{_m}=0;
    prc{_m}=prc{_m-1}*11/12 + prc{_m}/12;
  end;
run;

By retaining the values of PERC_1-PERC_10 from row to row, it's easy to divide by twelve and add 11/12th of the preceding month (i.e. for PERC_2 add 11/12th of PERC_1). Of course when starting a new sex, first reset PERC_1-PERC_10 to zero.

By the way, why do your have PERC_0 through PERC_10, and not through PERC_11?