Using Timestamp in SQL Query

[altatunc]

I am using a table value function in sas code to pull back a database table as of a certain date.  The database uses a timestamp format of YYYY-MM-DD HH:MM:SS and SAS is not wanting to use that or any equivalent when actually running the query.  A sample is below.

proc sql;
  create table TableAsOf_Jan162020 as
     select * from Libname.TableNameAsOf('2020-01-16 08:00:00');
quit;

I keep getting this error or something similar depending on the attempts at changing the format in the ( ).

ERROR: Invalid option name '2020-01-16 08:00:00'. 

All help appreciated!

[ballardw]

Is that value part of the table name in an external database? As the name is

TableNameAsOf('2020-01-16 08:00:00')

with ( and ' : and space characters when you view the name in the external DB???

You might try referencing the name as

"TableNameAsOf('2020-01-16 08:00:00')"n

if it uses non-standard characters for SAS names. This may require the system setting of VALIDMEMNAME=Extend as well.

[altatunc]

Thanks for the question.

I do not think so.  It is a table value function which returns another table as of that date value passed.  That other table is veiwable from the database (it is actually a table view though - i believe).

[altatunc]

From our IT...in our case, Table Valued Functions take parameters and return data, much like a table or view: in this case there is a single parameter (called @AsOf) and it is a GMT timestamp. The function then returns exactly what the table looked like at the time provided.

[PGStats]

Assuming that SAS interprets your database timestamps as SAS datetimes (i.e. numbers), SAS/SQL will understand a query such as

proc sql;
create table TableAsOf_Jan162020 as
select * 
from Libname.TableName
where timestamp <= '16JAN2020:08:00:00'dt;
quit;

If it sees them as strings you can get away with

proc sql;
create table TableAsOf_Jan162020 as
select * 
from Libname.TableName
where timestamp <= '2020-01-16 08:00:00';
quit;

[altatunc · Accepted solution]

So, I contacted SAS Support and they provided a solution...

I looked through our database and found some old tracks where sites had to query Table Value Functions passed to the database and it looks like the only way this can work is to use PROC SQL explicit pass-through syntax.  I've included a link below to the section of documentation that discusses this method of connecting to databases via the SAS/ACCESS Interface to ODBC engine.  I also attempted to modify the basic syntax in this documentation with your specifics.  I think the query below should work with one modification.  You'll have to find the schema associated with the table in the database and replace schema with the actual schema name.  Explicit pass-through passes native database syntax over to the database for processing.  Since you have to reference tables with schema.tablename syntax in the database, this also needs to be used in when passing the query over from SAS:

SAS/ACCESS to ODBC explicit pass-through documentation:  https://go.documentation.sas.com/?docsetId=acreldb&docsetTarget=p1f29m86u65hken1deqcybowtgma.htm&doc...

proc sql;
    connect to odbc (NOPROMPT=&DB READBUFF=100);
         create table TableAsOf_Jan162020 as
              select * from connection to odbc
                  ( select * from Schema/Libname.TableNameAsOf('2020-01-16 08:00:00') );
quit;

Hope that helps anyone else in the same problem.