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);
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;
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.
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);
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
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"
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.)
%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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.