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.
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.
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;
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?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.