topic Re: Help needed for date formatting in macro! in SAS Programming

Help needed for date formatting in macro!

stefflovessas — Thu, 09 Feb 2012 22:59:56 GMT

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

Help needed for date formatting in macro!

Tom — Thu, 09 Feb 2012 23:13:23 GMT

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

Re: Help needed for date formatting in macro!

Linlin — Fri, 10 Feb 2012 01:41:21 GMT

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

;

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

Help needed for date formatting in macro!

stefflovessas — Fri, 10 Feb 2012 13:35:51 GMT

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!

Help needed for date formatting in macro!

art297 — Fri, 10 Feb 2012 13:41:27 GMT

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

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

Re: Help needed for date formatting in macro!

stefflovessas — Fri, 10 Feb 2012 13:47:22 GMT

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

Re: Help needed for date formatting in macro!

stefflovessas — Fri, 10 Feb 2012 13:51:44 GMT

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.

Re: Help needed for date formatting in macro!

art297 — Fri, 10 Feb 2012 13:53:10 GMT

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

Re: Help needed for date formatting in macro!

Tom — Fri, 10 Feb 2012 13:55:16 GMT

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.

Re: Help needed for date formatting in macro!

stefflovessas — Fri, 10 Feb 2012 14:20:06 GMT

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

Re: Help needed for date formatting in macro!

art297 — Fri, 10 Feb 2012 14:33:37 GMT

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%2Fdoc%2Fr0008474.htm

Re: Help needed for date formatting in macro!

Linlin — Fri, 10 Feb 2012 14:38:33 GMT

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

;

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

;

Re: Help needed for date formatting in macro!

stefflovessas — Fri, 10 Feb 2012 14:44:30 GMT

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

Re: Help needed for date formatting in macro!

Linlin — Fri, 10 Feb 2012 15:08:15 GMT

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

Re: Help needed for date formatting in macro!

ballardw — Fri, 10 Feb 2012 16:47:22 GMT

Looks like the condition in SQL might call for a NOT BETWEEN:

sql-expression <NOT> BETWEEN sql-expression AND sql-expression

Re: Help needed for date formatting in macro!

Tom — Fri, 10 Feb 2012 16:59:41 GMT

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.

Re: Help needed for date formatting in macro!

stefflovessas — Fri, 10 Feb 2012 18:00:28 GMT

Thanks everyone, but unfortunately this is still giving me issues. It's just not understanding date formats in this macro, no matter which way i try to express them. when they're in date9, it gives me this error. is anyone familiar with the 'numeric literal'?

ERROR: CLI describe error: [IBM][CLI Driver][DB2/AIX64] SQL0103N The numeric literal "01JUL2009" is not valid. SQLSTATE=42604

Re: Help needed for date formatting in macro!

Tom — Fri, 10 Feb 2012 18:07:37 GMT

Look at this SAS publication. http://support.sas.com/techsup/technote/ts566a.pdf

It looks like DB2 wants datetime constants in the format 'YYYYMMDDHHMMSS'. It probably also wants the single quote characters rather than double quotes.

Re: Help needed for date formatting in macro!

FloydNevseta — Fri, 10 Feb 2012 18:37:51 GMT

I think you have one and/or two problems.

1. The date format ddmmmyyyy may not be interpreted as a valid date string in db2. You should try yymmdd10. which is recognized by many dbms'.

2. In SQL pass-through, you need to make sure that the sql statements formed by any resolved macros are in the form that is recognized by the dbms. For example, I often pass date values in macros to a Teradata database. I first format the dates as yymmdd10.. Then I have to enclose the date value in single quotes. So most Teradata sql statements involving dates are written like this in my SAS code: where start_date = %str(%')&dateval%str(%'). When the SAS code runs, the masked single quotes allow the macro dateval to resolve, but the resulting date parameter will have single quotes that get passed to the Teradata sql processor which is expecting single quotes around data strings. I haven't worked with db2 in many years, so I cannot recall if date strings require singel quotes.

Hope that helps.

Re: Help needed for date formatting in macro!

Patrick — Sat, 11 Feb 2012 01:49:39 GMT

I would suggest you send us some plain PROC SQL code which you've tested. Eg. something like below:

proc sql;

create view work.NB_ATT_plcytrmTEST as

select distinct * from connection to db2 (

select *

from edw.table_a

where effective_dt GE <hard coded date value in DB2 syntax>

and effective_dt LT <hard coded data value DB2 syntax>

;

);

quit;

And then you tell us exactly what you want to achieve - eg. create yearly SAS tables based on a start date and a number of years (you see I don't fully understand what you're after based on the code you've posted).

Looking at the initial macro code you've posted, I feel there could be an easier approach (eg: one query to the data base creating a view and then a SAS data step executing this view and splitting up the data into multiple SAS tables).