Hi there, I'm having some difficulty with date formatting in my macro. I'm trying to equate a mmddyy10. format with a sas integer date format. I'm trying to translate the dates in sas format into mmddyy10. format but it's giving me several errors. My code & log is below. Any help is much appreciated! Thanks in advance.
%let experience_dt= "05jan2012"d;
%let oldest_dt = %sysfunc(intnx(month,&experience_dt,-30,begin));
%let newest_dt = %sysfunc(intnx(month,&experience_dt,-2,end));
%let oldest_yr = %sysfunc(year(&oldest_dt));
%let newest_yr = %sysfunc(year(&newest_dt));
options obs=0;
%macro x();
%do i = &oldest_yr %to &newest_yr;
%let newyear = %sysfunc(intnx(month,"01jan&i"d,0,sameday));
%let old_dt = %sysfunc(inputn(&oldest_dt,mmddyy10.));
%let new_dt = %sysfunc(inputn(&newest_dt,mmddyy10.));
%let new_yr = %sysfunc(inputn(&newyear,mmddyy10.));
proc sql;
create table steff.NB_ATT_plcytrm&i as select distinct * from connection to db2 (
select *
from edw.table_a
where effective_dt >= %sysfunc(max(&old_dt,&new_yr))
and effective_dt < %sysfunc(min(&new_dt,&new_yr))
;
%end;
quit;);
%mend;
%x();
As you can see, I'm trying to use the inputn function to convert the date formatting but it's giving me these errors in the log:
SYMBOLGEN: Macro variable OLDEST_YR resolves to 2009
SYMBOLGEN: Macro variable NEWEST_YR resolves to 2011
SYMBOLGEN: Macro variable I resolves to 2009
SYMBOLGEN: Macro variable OLDEST_DT resolves to 18079
WARNING: Argument 1 to function INPUTN referenced by the %SYSFUNC or %QSYSFUNC macro function is out of range.
NOTE: Mathematical operations could not be performed during %SYSFUNC function execution. The result of the operations have been set
to a missing value.
SYMBOLGEN: Macro variable NEWEST_DT resolves to 18961
WARNING: Argument 1 to function INPUTN referenced by the %SYSFUNC or %QSYSFUNC macro function is out of range.
NOTE: Mathematical operations could not be performed during %SYSFUNC function execution. The result of the operations have been set
to a missing value.
SYMBOLGEN: Macro variable NEWYEAR resolves to 17898
WARNING: Argument 1 to function INPUTN referenced by the %SYSFUNC or %QSYSFUNC macro function is out of range.
NOTE: Mathematical operations could not be performed during %SYSFUNC function execution. The result of the operations have been set
to a missing value.
SYMBOLGEN: Macro variable USER resolves to u51pso
SYMBOLGEN: Macro variable BQPPASS resolves to {sas001}cmF3cjU2NTY=
SYMBOLGEN: Macro variable I resolves to 2009
SYMBOLGEN: Macro variable OLD_DT resolves to .
SYMBOLGEN: Macro variable NEW_YR resolves to .
SYMBOLGEN: Macro variable NEW_DT resolves to .
SYMBOLGEN: Macro variable NEW_YR resolves to .
ERROR: CLI describe error: [IBM][CLI Driver][DB2/AIX64] SQL0104N An unexpected token "." was found following "plcy_trm_efctv_dt
>=". Expected tokens may include: "<update_source_no_row_query>". SQLSTATE=42601
%let old_dt = %sysfunc(inputn(&oldest_dt,mmddyy10.));
SYMBOLGEN: Macro variable OLDEST_DT resolves to 18079
WARNING: Argument 1 to function INPUTN referenced by the %SYSFUNC or %QSYSFUNC macro function is out of range.
You have generated in OLDEST_DT the number of days since 1/1/1960. If you want to make that look like a date then you can use PUTN function. You probably do not even need convert it as you are just going to use the value 18079 later on in your SQL query.
You could also use a format for your SYSFUNC call back when you created OLDEST_DT.
%let oldest_dt = %sysfunc(intnx(month,&experience_dt,-30,begin),date9.);
...
where effective_dt >= max("&oldest_dt"d,"&new_yr"d)
You might also look into doing these calculations in a data step instead of macro code. Much easier to code and debug.
Thanks so much, Tom & Linlin. I actually tried Tom's suggestions first of putting a format in my %SYSFUNC and it worked! Now, it's just giving me one more small problem. Now the min and max functions are not understanding how to compare the dates because they're not numbers. Here's my code again and the corresponding log.
%do i = &oldest_yr %to &newest_yr;
%let newyear = %sysfunc(intnx(month,"01jan&i"d,0,sameday));
%let old_dt = %sysfunc(intnx(month,&oldest_dt,0,sameday),date9.);
%let new_dt = %sysfunc(intnx(month,&newest_dt,0,sameday),date9.);
%let new_yr = %sysfunc(intnx(month,&newyear,0,sameday),date9.);
proc sql;
create table steff.NB_ATT_plcytrm&i as select distinct * from connection to db2 (
select *
from abc.table_a
where effective_dt >= %sysfunc(max(&old_dt,&new_yr))
AND BSNS_BSS_CD = 'V'
and crprtn_yr_cn=1
and effective_dt < %sysfunc(min(&new_dt,&new_yr))
;
%end;
quit;);
ERROR: Argument 1 to function MIN referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number.
ERROR: Argument 2 to function MIN referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number.
ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list. Execution of %SYSCALL statement or %SYSFUNC or %QSYSFUNC function reference is terminated.
ERROR: CLI describe error: [IBM][CLI Driver][DB2/AIX64] SQL0104N An unexpected token "." was found following "effective_dt
>=". Expected tokens may include: "<update_source_no_row_query>". SQLSTATE=42601
THANKS!
Did you try it using the syntax that Tom had suggested?, namely:
where effective_dt >= max("&oldest_dt"d,"&new_yr"d)
When I do that, it gives me this:
SYMBOLGEN: Macro variable OLD_DT resolves to 01JUL2009
SYMBOLGEN: Macro variable NEW_YR resolves to 01JAN2010
SYMBOLGEN: Macro variable NEW_DT resolves to 30NOV2011
SYMBOLGEN: Macro variable NEW_YR resolves to 01JAN2010
ERROR: CLI describe error: [IBM][CLI Driver][DB2/AIX64] SQL0401N The data types of the operands for the operation ">=" are not
compatible or comparable. SQLSTATE=42818
So it looks like the dates are finally getting translated into the format that I want, because that's the same format that effective_dt is in. BUT, now min/max doesn't work on that format.
Is effective_dt a timestamp or datetime value rather than a date? If so, you have to compare apples with apples.
Looks like your WHERE clause is being pushed into a database. Where does the libref ABC point? You probably might need to format the dates in the syntax that your database wants.
The database format is a date with date9 format...
I've never worked with db2 but I don't think it has a date9. format. Are you sure it isn't one of the ones shown at: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.admin.doc%2Fdo...
Do you want to try changing your select statement from
select *
from abc.table_a
where effective_dt >= %sysfunc(max(&old_dt,&new_yr))
AND BSNS_BSS_CD = 'V'
and crprtn_yr_cn=1
and effective_dt < %sysfunc(min(&new_dt,&new_yr))
;
to
select *
from abc.table_a
where effective_dt >= %sysfunc(putn(%sysfunc(max(“&old_dt”d,”&new_yr”d)),date9.))
AND BSNS_BSS_CD = 'V'
and crprtn_yr_cn=1
and effective_dt < %sysfunc(putn(%sysfunc(min(“&new_dt”d,”&new_yr”d)),date9.))
;
When I do that, it goes back to the min/max functions not recognizing the dates are not numbers.
ERROR: Argument 1 to function MIN referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number.
ERROR: Argument 2 to function MIN referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number.
ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list. Execution of %SYSCALL statement or %SYSFUNC
or %QSYSFUNC function reference is terminated.
ERROR: CLI describe error: [IBM][CLI Driver][DB2/AIX64] SQL0103N The numeric literal "01JUL2009" is not valid. SQLSTATE=42604
It should work. this is how I tested:
%let i=2009;
%let v1 = %sysfunc(intnx(month,"01jan&i"d,0,sameday));
%let v2 = %sysfunc(intnx(month,&v1,0,sameday),date9.);
%let v3 = %sysfunc(intnx(month,"09jan&i"d,0,sameday));
%let v4 = %sysfunc(intnx(month,&v3,0,sameday),date9.);
%let v5=%sysfunc(max("&v2"d,"&v4"d));
%let v6=%sysfunc(min("&v2"d,"&v4"d));
%let v7=%sysfunc(putn(&v5,date9.));
%let v8=%sysfunc(putn(&v6,date9.));
%put &v1 &v2 &v3 &v4 &v5 &v6 &v7 &v8;
From log file:
165
166 %let i=2009;
167 %let v1 = %sysfunc(intnx(month,"01jan&i"d,0,sameday));
168 %let v2 = %sysfunc(intnx(month,&v1,0,sameday),date9.);
169 %let v3 = %sysfunc(intnx(month,"09jan&i"d,0,sameday));
170 %let v4 = %sysfunc(intnx(month,&v3,0,sameday),date9.);
171 %let v5=%sysfunc(max("&v2"d,"&v4"d));
172 %let v6=%sysfunc(min("&v2"d,"&v4"d));
173
174 %let v7=%sysfunc(putn(&v5,date9.));
175 %let v8=%sysfunc(putn(&v6,date9.));
176
177 %put &v1 &v2 &v3 &v4 &v5 &v6 &v7 &v8;
17898 01JAN2009 17906 09JAN2009 17906 17898 09JAN2009 01JAN2009
Test just writing the SQL query without any macro code to see what format the command needs to be to work.
If your source data is in a SAS dataset then it will recognize syntax like
effective_dt >= "01JAN2009"d
without syntax errors. Now if EFFECTIVE_DT is actually a DateTime variable instead of a Date variable then all records will have values greater than any resonable date value.
If instead your source data is in an Oracle, DB2 or other external database then it might give you a syntax error because SAS is trying to push the WHERE condition into the database and that database uses a different format for date literals.
Hi,
I think your code is the same as mine. the condition in proc sql will never be true.
data have;
input id effective_dt mmddyy10.;
cards;
1 01/01/2009
2 11/30/2009
3 01/01/2010
4 11/30/2010
5 01/01/2011
6 11/30/2011
7
;
%let experience_dt= "05jan2012"d;
%let oldest_dt = %sysfunc(intnx(month,&experience_dt,-30,begin));
%let newest_dt = %sysfunc(intnx(month,&experience_dt,-2,end));
%let oldest_yr = %sysfunc(year(&oldest_dt));
%let newest_yr = %sysfunc(year(&newest_dt));
%put &experience_dt
&oldest_dt
&newest_dt
&oldest_yr
&newest_yr;
%macro x();
%do i = &oldest_yr %to &newest_yr;
%let newyear = %sysfunc(mdy(1,1,&i));
%let old_dt = &oldest_dt;
%let new_dt = &newest_dt;
%let new_yr = &newyear;
proc sql;
create table want&i as select *
from have
where effective_dt >= %sysfunc(max(&old_dt,&new_yr))
and effective_dt < %sysfunc(min(&new_dt,&new_yr))
;
%end;
quit;
%mend;
%x();
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.