DATA Step, Macro, Functions and more

Help needed for date formatting in macro!

Reply
Occasional Contributor
Posts: 7

Help needed for date formatting in macro!

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;

quitSmiley Wink;

%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

Super User
Super User
Posts: 6,497

Help needed for date formatting in macro!

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

Occasional Contributor
Posts: 7

Help needed for date formatting in macro!


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;

quitSmiley Wink;

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!

PROC Star
Posts: 7,356

Help needed for date formatting in macro!

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

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

Occasional Contributor
Posts: 7

Re: Help needed for date formatting in macro!

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

Occasional Contributor
Posts: 7

Re: Help needed for date formatting in macro!

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.

PROC Star
Posts: 7,356

Re: Help needed for date formatting in macro!

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

Super User
Super User
Posts: 6,497

Re: Help needed for date formatting in macro!

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.

Occasional Contributor
Posts: 7

Re: Help needed for date formatting in macro!

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

PROC Star
Posts: 7,356

Re: Help needed for date formatting in macro!

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

Super Contributor
Posts: 1,636

Re: Help needed for date formatting in macro!

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

;

Occasional Contributor
Posts: 7

Re: Help needed for date formatting in macro!

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

Super Contributor
Posts: 1,636

Re: Help needed for date formatting in macro!

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

Super User
Super User
Posts: 6,497

Re: Help needed for date formatting in macro!

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.

Super Contributor
Posts: 1,636

Re: Help needed for date formatting in macro!

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

Ask a Question
Discussion stats
  • 21 replies
  • 6730 views
  • 0 likes
  • 8 in conversation