DATA Step, Macro, Functions and more

How to filter Db2 dates in SAS dataset?

Reply
PROC Star
Posts: 633

How to filter Db2 dates in SAS dataset?

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

 

Super User
Posts: 10,574

Re: How to filter Db2 dates in SAS dataset?

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
PROC Star
Posts: 633

Re: How to filter Db2 dates in SAS dataset?

Posted in reply to KurtBremser
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.
Super User
Posts: 10,574

Re: How to filter Db2 dates in SAS dataset?


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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 5,917

Re: How to filter Db2 dates in SAS dataset?

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.
Data never sleeps
PROC Star
Posts: 633

Re: How to filter Db2 dates in SAS dataset?

I was asked to use explicit pass-through.
Super User
Posts: 10,574

Re: How to filter Db2 dates in SAS dataset?


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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Respected Advisor
Posts: 4,797

Re: How to filter Db2 dates in SAS dataset?


@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/r000710...

Ask a Question
Discussion stats
  • 7 replies
  • 118 views
  • 2 likes
  • 4 in conversation