topic Re: How to filter Db2 dates in SAS dataset? in SAS Programming

How to filter Db2 dates in SAS dataset?

Babloo — Wed, 11 Apr 2018 15:03:01 GMT

I've the following code to filter values in Db2 table but I end up with error like "CLI describe error: [IBM][CLI Driver][DB2/LINUXX8664] SQL0103N The numeric literal "31MAR2018" is not valid. SQLSTATE=42604"

Value of macro variable REPORTING_DATE resolves to 31MAR2018. Could you please help me to resolve this issue?

select * 
	    from connection to &glb_db2. 
			( 
			SELECT DISTINCT
			D1IPAR,
			MTD
	     	FROM U.TBFQ81
			WHERE D1NDBT = 'TBFR22' and PDK = '4' AND D1IPAR is not null and MTD=&REPORTING_DATE.
			)
        ;

Re: How to filter Db2 dates in SAS dataset?

Kurt_Bremser — Wed, 11 Apr 2018 17:09:35 GMT

DB/2 uses a different format for date literals. Ask your DB/2 people how you need to provide the date in the pass-through. Or do a google search for "db2 date literal".

Re: How to filter Db2 dates in SAS dataset?

Babloo — Wed, 11 Apr 2018 17:48:37 GMT

Sure. Just to inform you that I could see the DB2 table (might be
registered) in SAS DI Studio and it has the value 31MAR2018 (which I'm
looking for) and it has date9. format.

Re: How to filter Db2 dates in SAS dataset?

Kurt_Bremser — Wed, 11 Apr 2018 18:16:44 GMT

@Babloo wrote:
Sure. Just to inform you that I could see the DB2 table (might be
registered) in SAS DI Studio and it has the value 31MAR2018 (which I'm
looking for) and it has date9. format.

That's because SAS uses its own display formats in its world. But in an explicit pass-through, you have to adhere to the rules of the DBMS.

Re: How to filter Db2 dates in SAS dataset?

LinusH — Thu, 12 Apr 2018 02:51:21 GMT

Using explicit pass through is over complicating nd should only be used when performance improvements can be expected, which is not the case here.
Use a libname connection and you don't have to bother about target RDBMS date formats.

Re: How to filter Db2 dates in SAS dataset?

Babloo — Thu, 12 Apr 2018 05:39:37 GMT

I was asked to use explicit pass-through.

Re: How to filter Db2 dates in SAS dataset?

Kurt_Bremser — Thu, 12 Apr 2018 05:55:55 GMT

@Babloo wrote:
I was asked to use explicit pass-through.

Then you have to use DB/2 syntax, period.

BTW as a SAS developer, it is often your job to make it clear to persons who have no clue (at least about SAS) which solution is better. Just that somebody says "do it this way" does in no way implicate they know what they're talking about. In fact, people trying to force you to do something in their preferred way is usually a sign of galloping stupidity on their side.

Re: How to filter Db2 dates in SAS dataset?

Patrick — Thu, 12 Apr 2018 09:12:31 GMT

@Babloo wrote:

I've the following code to filter values in Db2 table but I end up with error like "CLI describe error: [IBM][CLI Driver][DB2/LINUXX8664] SQL0103N The numeric literal "31MAR2018" is not valid. SQLSTATE=42604"

Value of macro variable REPORTING_DATE resolves to 31MAR2018. Could you please help me to resolve this issue?
select * 
	    from connection to &glb_db2. 
			( 
			SELECT DISTINCT
			D1IPAR,
			MTD
	     	FROM U.TBFQ81
			WHERE D1NDBT = 'TBFR22' and PDK = '4' AND D1IPAR is not null and MTD=&REPORTING_DATE.
			)
        ;

Like in SAS you need to tell the DB2 that the string you're passing in needs to get converted to a datetime value. Just Googling in quickly it appears that DB2 has a TO_DATE() function for this.

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0007109.html