BookmarkSubscribeRSS Feed
stefflovessas
Calcite | Level 5

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

21 REPLIES 21
Tom
Super User Tom
Super User

%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.

stefflovessas
Calcite | Level 5


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!

art297
Opal | Level 21

Did you try it using the syntax that Tom had suggested?, namely:

where effective_dt >= max("&oldest_dt"d,"&new_yr"d)

stefflovessas
Calcite | Level 5

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

stefflovessas
Calcite | Level 5

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.

art297
Opal | Level 21

Is effective_dt a timestamp or datetime value rather than a date?  If so, you have to compare apples with apples.

Tom
Super User Tom
Super User

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.

stefflovessas
Calcite | Level 5

The database format is a date with date9 format...

art297
Opal | Level 21

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...

Linlin
Lapis Lazuli | Level 10

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.))

;

stefflovessas
Calcite | Level 5

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

Linlin
Lapis Lazuli | Level 10

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

Tom
Super User Tom
Super User

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.

Linlin
Lapis Lazuli | Level 10

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();

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 21 replies
  • 14564 views
  • 1 like
  • 8 in conversation