topic unable to read date value while connected to database in SAS Programming

unable to read date value while connected to database

rohithverma — Fri, 14 Aug 2020 13:30:49 GMT

Unable to read the value of date column while fetching from database . Please help me out.

The corresponding date value is present in the following format as mentioned below where closeddate appears as date variable .

The following error is occurring if we use any conversion functions

ERROR: PROC SQL requires any created table to have at least 1 column.

PROC SQL;
CONNECT TO ODBC(
DATAsrc='DATALAKE'
USER='****'
password='******'
);
create table main as
select * from
connection to odbc
(
SELECT
*
FROM
PROD.CASE_S A
where A.closedate='20MAR2018:17:06:35.000000'
)
;

disconnect from odbc;
quit;

Re: unable to read date value while connected to database

Tom — Fri, 14 Aug 2020 13:32:07 GMT

Looks like you need help with the syntax used in your remote database? Does the query you posted work? Does it generate an error? What is your actual issue?

What database are you connecting to? How does that database want users to specify a literal datetime value in code?

Re: unable to read date value while connected to database

rohithverma — Fri, 14 Aug 2020 14:12:02 GMT

By syntax wise it was working fine but it was not capturing the date value in the closeddate column . It leads to the below error when we try to use to_char or any function in the where condition in order to filter the data. Please help me on this if possible .

ERROR: PROC SQL requires any created table to have at least 1 column.