topic How can i calculate age by "yrdif" in macro function ? in SAS Programming

How can i calculate age by "yrdif" in macro function ?

EvansHsieh — Thu, 24 Dec 2020 04:03:42 GMT

I created a macro function to calculated age between two date and filtered data with specific time interval. However, i didn't understand why the following function output with 'age' as '.' just like the below figure.

%let a = 10;
%macro filter_age(age_start, age_end);

%local today_;
%let today_ = %sysfunc(intnx(year, %sysfunc(today()), 0), date9.);

proc sql outobs = &a;
create table filter_age as
select distinct cif_num, yrdif(birth_dt, "&today_"d, 'actual') as age
from test

where calcuated age between &age_start and &age_end
quit;
%mend filter_age;
%filter_age(10, 20);

Re: How can i calculate age by "yrdif" in macro function ?

japelin — Thu, 24 Dec 2020 07:21:27 GMT

I think your code has syntax error.

Is there errors in log windows?

You're trying to test this code, aren't you?

/* 1. calcuated=>CALCUATED */
/* 2. Semicolon at the end of sql procedure */
where CALCULATED age between &age_start and &age_end;

Re: How can i calculate age by "yrdif" in macro function ?

Shmuel — Thu, 24 Dec 2020 07:20:56 GMT

What format do you expect that &toady_ will be?

Add %put &today_ after the line

%let today_ = %sysfunc(intnx(year, %sysfunc(today()), 0), date9.);

I assume you miss function PUT in above line.

Re: How can i calculate age by "yrdif" in macro function ?

EvansHsieh — Thu, 24 Dec 2020 08:12:17 GMT

In fact, the figure was generated from

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

%macro filter_age(age_start, age_end);
%local today_;
%let today_ = %sysfunc(intnx(year, %sysfunc(today()), 0), date9.);

proc sql outobs = &a;
create table filter_age as
select distinct cif_num, yrdif(birth_dt, "&today_"d, 'actual') as age
from test
quit;
%mend filter_age;
%filter_age(10, 20);

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

"test" data is

CIF_Num Birth_Dt

123	12NOV1993
1234	16AUG1976
12345	29OCT1964

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

and what my expected is that after input the age interval, I can create a table including all of information about this specific generation. That is why I add "where calcuated age between &age_start and &age_end"

Re: How can i calculate age by "yrdif" in macro function ?

EvansHsieh — Thu, 24 Dec 2020 08:22:14 GMT

Beacuse I want to create table by
--------------------------------------------
proc sql outobs = 10;
create table filter_age as
select distinct cif_num, yrdif(birth_dt, today() 'actual') as age
from test;
quit;
------------------------------------
but if I wanna put it in macro function, i need to transform "today()" into macro variables. Also, i'm quit sure how can i apply with "yrdif" function in macro.

Re: How can i calculate age by "yrdif" in macro function ?

Shmuel — Thu, 24 Dec 2020 08:49:24 GMT

Next code works fine. I have added a test row:

data test;
 infile cards dlm='09'x;
 input CIF_Num  Birth_Dt date9.;
cards;
123	12NOV1993
1234	16AUG1976
12345	29OCT1964
77777	20MAY2005
; run;

%let a = 10;
%macro filter_age(age_start, age_end);

%local today_;
%let today_ = %sysfunc(intnx(year, %sysfunc(today()), 0), date9.);
%put TODAY_ = &today_;
proc sql outobs = &a;
create table filter_age as
select distinct cif_num, yrdif(birth_dt, "&today_"d, 'actual') as age
from test
where calculated age between &age_start and &age_end;
quit;
%mend filter_age;
%filter_age(10, 20);

Re: How can i calculate age by "yrdif" in macro function ?

PaigeMiller — Thu, 24 Dec 2020 11:40:30 GMT

Save yourself some potential future trouble and some typing by not formatting macro variables taht will be used for arithmetic or logical operations. See Maxim 28. (You still need to format them if you need a human to read the values)

%let today_ = %sysfunc(intnx(year, %sysfunc(today()), 0));

and then

select distinct cif_num, yrdif(birth_dt, &today_, 'actual') as age

This formatting of macro variables is unnecessary and seems to cause lots of people problems. (Of course, the other points made by the other replies are still valid and required to make this code work.)

Re: How can i calculate age by "yrdif" in macro function ?

Kurt_Bremser — Thu, 24 Dec 2020 12:42:12 GMT

%macro filter_age(age_start, age_end);

%local today_;
%let today_ = %sysfunc(today());
proc sql outobs = &a;
create table filter_age as
  select
  distinct cif_num,
  yrdif(birth_dt, &today_.,'actual') as age
  from test
  where calcuated age between &age_start and &age_end
;
quit;
%mend filter_age;

Macro variables need no formats (Maxim 28), and the INTNX function with a value of 0 and no modifier will result in an unchanged value. In case this does not lead to the expected result, please post the log.