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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.