BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
HCL1991
Calcite | Level 5

I'm trying to query a field named requestdate which is of the datetimeoffset data type from a table named maternitypackages inside a sql server database. If I use a libname statement I can succesfully connect to the database and access the maternitypackage table.

 

libname _tt   oledb provider=SQLNCLI11.1 dsn="pwt9094" schema=dbo properties=('Initial Catalog'=MaternityPackageModule 'Integrated Security'=SSPI) uid="" pwd="" dbmax_text=1024 preserve_tab_names=yes access=readonly prompt=no;

But the resulting maternitypackages table does not contain the requestdate field. 

 

 

If I use the sql passthrough facility of sas I can access this field if I cast it as datetime:

proc sql;
connect to oledb as mpm
  (provider=SQLNCLI11.1 dsn=pwt9094 schema=dbo 
properties=('Initial Catalog'=MaternityPackageModule 'Integrated Security'=SSPI)
uid="" pwd="" dbmax_text=1024 prompt=no);
create table ss as 
select * from connection to mpm (select cast(requestdate as datetime) as requestdate from maternitypackages);
;quit;

If I use the sql passthrough facility to access this field without casting I get an error: "ERROR: At least one of the columns in this DBMS table has a datatype that is not supported by this engine."

proc sql;
connect to oledb as mpm
  (provider=SQLNCLI11.1 dsn=pwt9094 schema=dbo 
properties=('Initial Catalog'=MaternityPackageModule 'Integrated Security'=SSPI)
uid="" pwd="" dbmax_text=1024 prompt=no);
create table ss as 
select * from connection to mpm (select requestdate from maternitypackages
                                     );
;quit;

Is casting using the sql passthough facility or a view from the database the only way to fix this problem? What is the best practice in this use case?

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

SAS only has two types of variables, floating point numbers and fixed length character strings.  To allow you to work with datetime values SAS has some formats, informats and functions that treat the number of seconds since 1960 as a datetime value.  But they do not have any concept of storing an offset from GMT in addition to the number of seconds.  So for this data to even be usable in SAS you will have to transform it in some way.  For example you could eliminate the offset by adjusting the value into either GMT or the servers local time.  I assume that is what you say worked for you.  Or you could translate it into a string that include the datetime value and the offset.  Or store the offset and datetime value as two separate variables.

 

It sounds like the OLEDB driver you are using does not know how to convert that data type for you.

You might try using the DBSASTYPE= dataset option and see if SAS will cast the value to a character string that you can read.

View solution in original post

3 REPLIES 3
JosvanderVelden
SAS Super FREQ
Does this not https://support.sas.com/kb/39/188.html help?

Best regards, Jos
HCL1991
Calcite | Level 5

Hi,

 

Do you mean that I should use 

SQLNCLI10.1

 instead of 

SQLNCLI11.1

as provider and this issue will be resolved? If that's the case I can contact our systems administrator to install SQLNCLI10.1. Please verify my assumption.

Tom
Super User Tom
Super User

SAS only has two types of variables, floating point numbers and fixed length character strings.  To allow you to work with datetime values SAS has some formats, informats and functions that treat the number of seconds since 1960 as a datetime value.  But they do not have any concept of storing an offset from GMT in addition to the number of seconds.  So for this data to even be usable in SAS you will have to transform it in some way.  For example you could eliminate the offset by adjusting the value into either GMT or the servers local time.  I assume that is what you say worked for you.  Or you could translate it into a string that include the datetime value and the offset.  Or store the offset and datetime value as two separate variables.

 

It sounds like the OLEDB driver you are using does not know how to convert that data type for you.

You might try using the DBSASTYPE= dataset option and see if SAS will cast the value to a character string that you can read.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 2020 views
  • 0 likes
  • 3 in conversation