topic Re: Date format changing in SQL database from datetime to date. How to use in SAS? in SAS Programming

Date format changing in SQL database from datetime to date. How to use in SAS?

Brian_lewis67 — Fri, 07 Oct 2016 13:07:44 GMT

In addition to the query I raised recently, someone has decided to amend all the date fields in several SQL databases from DateTime to Date.

Historically, we've read in the datetime fields and using datepart created a SAS date variable as follows:

format startdate ddmmyy10.;

startdate = datepart(start);

if startdate >= '01apr2016'd;

This no longer works. i'm using SAS 9.4 and have tried various methods to use the new date fields without success. I've read in a couple of places that SAS can't use Date format fields without using ProcSQL

Is this true or can I modify the code above (it's used in a lot of programmes for many fields) with minimal change? I want to avoid break it up into individual characters and then concatenating to create a new field.

Thanks in advance.

Brian

Re: Date format changing in SQL database from datetime to date. How to use in SAS?

Kurt_Bremser — Fri, 07 Oct 2016 13:17:47 GMT

First of all, make sure that the new date field is actually imported into SAS as date. Then omit the datepart conversion.

The column in SAS should be numeric and formatted with one of the date formats. And have values that make sense, of course.

If that is not the case, review the import process.

Re: Date format changing in SQL database from datetime to date. How to use in SAS?

LinusH — Fri, 07 Oct 2016 13:19:12 GMT

As in many ODBC (or are you using OLEDB?) trouble shooting situations, accessing the same data from another application is one way to understand the issue.

According to the doc, the SQL_TYPE_DATE will be interpreted as date in SAS.

If your date columns is categorized in ODBC as SQL_TYPE_TIMESTAMP, try use the DBTYPE= data set option.

Re: Date format changing in SQL database from datetime to date. How to use in SAS?

Brian_lewis67 — Fri, 07 Oct 2016 14:27:24 GMT

Thanks for pointing me in the right direction. It atually reads the date in as text $10. so I've used the input function.

startdate = input(start,yymmdd10.)

and this appears to have worked. Should this cause any issues when then comparing two dates?

Thanks,

Brian

Re: Date format changing in SQL database from datetime to date. How to use in SAS?

Brian_lewis67 — Fri, 07 Oct 2016 14:28:51 GMT

Apologies Linus but most of that makes any sense given my very, very limited knowledge. Thanks anyway.

Re: Date format changing in SQL database from datetime to date. How to use in SAS?

Patrick — Fri, 07 Oct 2016 15:03:17 GMT

When interacting with a Database you should try and get to a level where you understand what @LinusH is talking about.

The following link provides you the information to what data type and format the SAS Access engine converts SQL Server data types when pulling the data into SAS.

http://support.sas.com/documentation/cdl/en/acreldb/69039/HTML/default/viewer.htm#n0h4i25zq3t58en1lgr8jjx1rads.htm

That you end up with a SAS character variable of length 10 strongly indicates that the "date" in SQL Server is now actually stored as a string and most likely using a SQL Server variable of data type SQL_VARCHAR or SQL_CHAR.

Re: Date format changing in SQL database from datetime to date. How to use in SAS?

Brian_lewis67 — Fri, 07 Oct 2016 18:31:38 GMT

Hi Patrick,

Thanks for the link. That certainly explains how data types are treated when reading them in from an SQL database.

Unfortunately I'm not an expert at SQL or SAS but have been tasked with updating programme my team use as a result of server and Database changes.

It was a case of we are making these changes without consultation with users and offer no support either.

Regards,

Brian

Re: Date format changing in SQL database from datetime to date. How to use in SAS?

Kurt_Bremser — Sat, 08 Oct 2016 08:02:38 GMT

@Brian_lewis67 wrote:

Thanks for pointing me in the right direction. It atually reads the date in as text $10. so I've used the input function.

startdate = input(start,yymmdd10.)

and this appears to have worked. Should this cause any issues when then comparing two dates?

Thanks,

Brian

Just to be sure, apply a date format to startdate.

format startdate date9.;

This displays the date in human-readable format and allows you to verify that the values are correct. All comparisons will then work as expected.

Since I read database data not from the database itself, but from flat files unloaded from a database snapshot, I always get date values as CHAR(10) and have to read into a temprorary variable and then convert by using the input() function.

Re: Date format changing in SQL database from datetime to date. How to use in SAS?

LinusH — Sat, 08 Oct 2016 09:30:21 GMT

No problem, and sorry for being ignorant about your knowledge level. I'm glad that @Patrick chimed in and saved the day 😎

Re: Date format changing in SQL database from datetime to date. How to use in SAS?

Brian_lewis67 — Sun, 09 Oct 2016 00:01:48 GMT

There is no need to apologise. Thanks for your input.

Re: Date format changing in SQL database from datetime to date. How to use in SAS?

bnawrocki — Tue, 22 Nov 2016 14:45:18 GMT

I think I have the same problem. We run SAS code to import our month-end SQL Server database tables into SAS datasets. There are dozens of tables, with code such as this:

PROC SQL dquote=ansi ;
           create table dd_admin_trail as
              (select * from  ncedss.dd_admin_trail);
              quit;

In September, all date fields in SQL Server were stored as datetime fields, and imported into SAS correctly as datetime fields. In October, a change was made to SQL Server - we upgraded to SQL Server 2008 and all date fields are now stored as "DATE" fields (I checked in SQL Server, and the fields have a "DATE" type), but they import into SAS as CHAR fields of format/length $10, which are not easy to work with in SAS code.

How can I get SAS to import these DATE fields in SQL Server into numeric date fields in SAS? Shouldn't that just happen by default?

I found a way to force SAS to do this, by hard-coding a couple of date fields like in this code, but there are dozens of date fields across dozens of SQL Server tables, and I don't want to write this DBSASTYPE clause for EVERY one of them!

PROC SQL;
   create table DD_ADMIN_TRAIL as
     select * from  ncedss.DD_ADMIN_TRAIL (dbsastype=(ASSIGNED_TO_DT='DATE' CDC_BASE_DT='DATE') );
quit;

Re: Date format changing in SQL database from datetime to date. How to use in SAS?

Patrick — Tue, 22 Nov 2016 22:15:15 GMT

Please create a new question for a new problem and just post a reference to similar questions/solutions you found.

It's likely that you're using the wrong ODBC driver, which is something your SAS Admin/SAS Installer would have to change.

http://support.sas.com/kb/40/080.html

Re: Date format changing in SQL database from datetime to date. How to use in SAS?

bnawrocki — Tue, 29 Nov 2016 16:26:26 GMT

Thanks, Patrick! I should have searched the SAS Usage Notes first, before posting here. Yes, I am using the "SAS/ACCESS Interface to OLE DB" driver to access SQL Server data from SAS code.

After reading the link you provided, it sounds like that should fix my problem. I'll try it out next week, when everyone is back in the office after Thanksgiving break.

UPDATE: It worked! Based on a similar Usage Note for SAS/ACCESS Interface to OLE DB - http://support.sas.com/kb/39/188.html -- we just changed our PROVIDER= parameter to Provider=SQLNCLI10.1 in our LIBNAME xxx OLEDB ...; statement, and the date fields are now downloading correctly from SQL Server 2008 into SAS numeric date variables.