topic Macro to calculate age as of each year in SAS Programming

Macro to calculate age as of each year

kevsma — Wed, 15 Feb 2023 21:24:18 GMT

Hello,

I have a year character variable (2014, 2015, 2016... 2022) and I want to write a macro that calculates age as of the end of each year so that i do not need to hard code it every single time like below.

I believe this should be a fairly easy macro, can anyone help out? Thanks!

data edd_sc_cder; set edd_sc_cder;
	cy=substr(edd_qrt, 1,4);
	if cy="2014" then age=floor((intck('month',birthday,'31dec2014'd)-(day('31dec2014'd) < day(birthday)))/12); 
		else if fy="2015" then age=floor((intck('month',birthday,'31dec2015'd)-(day('31dec2015'd) < day(birthday)))/12); 
		else if fy="2016" then age=floor((intck('month',birthday,'31dec2016'd)-(day('31dec2016'd) < day(birthday)))/12); 
		else if fy="2017" then age=floor((intck('month',birthday,'31dec2017'd)-(day('31dec2017'd) < day(birthday)))/12); 
		else if fy="2018" then age=floor((intck('month',birthday,'31dec2018'd)-(day('31dec2018'd) < day(birthday)))/12); 
		else if fy="2019" then age=floor((intck('month',birthday,'31dec2019'd)-(day('31dec2019'd) < day(birthday)))/12); 
		else if fy="2020" then age=floor((intck('month',birthday,'31dec2020'd)-(day('31dec2020'd) < day(birthday)))/12); 
		else if fy="2021" then age=floor((intck('month',birthday,'31dec2021'd)-(day('31dec2021'd) < day(birthday)))/12); 
		else if fy="2022" then age=floor((intck('month',birthday,'31dec2022'd)-(day('31dec2022'd) < day(birthday)))/12); 
run;

Re: Macro to calculate age as of each year

ballardw — Wed, 15 Feb 2023 21:53:49 GMT

@kevsma wrote:

Hello,

I have a year character variable (2014, 2015, 2016... 2022)

If you want to "caluculate" then use numeric variables. Even if you just drop it later. Then you can use MDY(12,31,numyear) to get the DATE of a specific day of the year.

Try looking at the YRDIF function

age = floor( yrdif(birthday,mdy(12,31,input(fy,4.))));

Input turns the character value into a numeric. The function MDY returns a date, in this case the last day of a calendar year.

YRDIF by default returns an age with a decimal fraction given a start date and end date.

Re: Macro to calculate age as of each year

kevsma — Wed, 15 Feb 2023 21:59:27 GMT

Thanks @ballardw will convert it to numeric. Any suggestions as how to write the macro? Thank you!

Re: Macro to calculate age as of each year

ballardw — Wed, 15 Feb 2023 22:02:55 GMT

@kevsma wrote:

Thanks @ballardw will convert it to numeric. Any suggestions as how to write the macro? Thank you!

No macro needed, it is a numeric formula. Did you try the code shown in place of your "if/then" block of code with your data?

Re: Macro to calculate age as of each year

Reeza — Wed, 15 Feb 2023 22:33:33 GMT

Assuming the change from cy to fy is a typo, you can modify your code to be dynamic as follows:

data edd_sc_cder; 
set edd_sc_cder;
	cy=input(substr(edd_qrt, 1,4), 8.);
         index_date = mdy(12, 31, cy);
         format index_date date9.;
	 age=floor((intck('month',birthday,index_date)-(day(index_date) < day(birthday)))/12); 
		
run;

You could simplify this further, embedding the date calculation into the code but this makes it clear that you're always calculating age as of the last day of the year.

Usually July1st (mid year is used) but I'm assuming you have a reason for doing it as of end of year 🙂

Re: Macro to calculate age as of each year

MayurJadhav — Wed, 15 Feb 2023 22:36:22 GMT

I don't understand why you're calculating fy and using it in IF-ELSE statement. I believe there would be some good reason but I can't tell without knowing an input data and no understanding exact requirement.

Though it seems you're only interested in current FY year to calculate age. If so then you can have only one following IF statement. You just need to have dynamic current year to compare with your FY. And replace hard-coded last day of the year date with dynamic date using intnx('year',today(),0,'E')

if fy = year(today()) then age = floor((intck('month',birthday,intnx('year',today(),0,'E'))-(day(intnx('year',today(),0,'E')) < day(birthday)))/12);

In case if you have complex input dataset and want to use sas macro then here one simple example:

/* Macro that calculate age */
%macro age(date, birth);
	floor((intck('month', &birth, &date) - (day(&date) < day(&birth))) / 12) 
	
%mend age;

data _NULL_;
	Birthday='11NOV1990'd;

	/* i assume you have birthday date variable in the input data set hence can be easily replaced */
	fy=2023;

	/* i assume you have fy variable in the input data set hence can be easily replaced */
	LastDayOftheYear=intnx('year', today(), 0, 'E');

	if fy=year(today()) then
		age=%age(LastDayOftheYear, Birthday);
	put Birthday=date9. LastDayOftheYear=date9.;
	put age=;
run;

Re: Macro to calculate age as of each year

kevsma — Thu, 16 Feb 2023 17:37:35 GMT

Thanks @ballardw this works perfectly!!

Re: Macro to calculate age as of each year

kevsma — Thu, 16 Feb 2023 17:43:39 GMT

Thanks for the input, I guess my main question is if I have a longitudinal dataset with a time variable (fy, cy, quarter, etc), how to grab the age by the end of each time period? It looks like trying to use a macro is over-complicating the issue.

Re: Macro to calculate age as of each year

Tom — Thu, 16 Feb 2023 18:05:25 GMT

@kevsma wrote:

Thanks for the input, I guess my main question is if I have a longitudinal dataset with a time variable (fy, cy, quarter, etc), how to grab the age by the end of each time period? It looks like trying to use a macro is over-complicating the issue.

Macro logic is used to manipulate text, for example to generate SAS code.

To work with actual data, like the value of a FY or CY variable, use actual SAS code.