topic Re: Uploading data to SQL server - issue with Date variable in SAS Procedures

Uploading data to SQL server - issue with Date variable

DangIT — Fri, 14 Nov 2014 14:26:29 GMT

Hi,

I am trying to write a dataset to SQL server from SAS, but the data has a Date variable, that doesn't convert correctly.

I am simply using a Libname reference to the SQL server and running a Data step to output into SQL.

For example.

Data SQL.Table_name ; Set WORK.Table_Name ; run ;

When I check the table on the SQL server, the date variable is converted to a datetime variable and obviously the values are incorrect.

How can I correctly write a table to SQL server inorder to deal with Date variables?

Thanks in advance for your help.

Re: Uploading data to SQL server - issue with Date variable

Ksharp — Sat, 15 Nov 2014 09:36:19 GMT

Did you assign a date format to that sas date variable ?

format your_variable date9. ;

Re: Uploading data to SQL server - issue with Date variable

jakarman — Sat, 15 Nov 2014 10:07:06 GMT

Review the SAS/Access interface type conversions. SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition

The default type conversions are done by the variable format attribute. It can be overwritten by a dbtype option.

Sometimes it smarter when using bulkload processes to do the loading in two steps. SQL-server is not having this type interface.

1/ Create an empty dataset with correct type settings in the dbbms

2/ Fill the data (proc append or whatever)
Why should you do something like this? It is about avoiding IO and that availability of formats on creating those DBMS tables.

Re: Uploading data to SQL server - issue with Date variable

Reeza — Sat, 15 Nov 2014 17:05:20 GMT

SQL server only has datetime variables so you'll need to convert your date variables to datetime variables.

You can use the function dhms(date, 0, 0, 0).

Re: Uploading data to SQL server - issue with Date variable

Patrick — Sat, 15 Nov 2014 23:28:57 GMT

The SAS/ACCESS engine should handle the conversion of a SAS Date to a SQL_DATE correctly (refer to link posted by ) as long as there is a known standard SAS Date Format assigned to the SAS variable (as suggested by ).

Re: Uploading data to SQL server - issue with Date variable

Patrick — Sat, 15 Nov 2014 23:32:47 GMT

Make sure you have a standard SAS Date Format assigned to the SAS variable (so not some custom picture format but something like date9.).

Add the following option before your SQL or data step. This will show you in the SAS log what the SAS/ACCESS engine actually does and what SQL it sends to your data base. So you should see in the log if and what conversion happens.

options sastrace=",,,d" sastraceloc=saslog nostsuffix;

Re: Uploading data to SQL server - issue with Date variable

DangIT — Mon, 17 Nov 2014 18:43:27 GMT

Is that true?

I have tables in SQL Server with just a Date format (no time) and I was able to use a BULK INSERT from a text file to upload the data as a Date format.

For example, I would create the table as:

Create TABLE [temp](

[Date] Date ,

[Sales] Real

);

and use BULK INSERT to upload the text file where the Date variable has no time element.

Does SQL Server automatically convert the Date values to datetime and then drop the time element?

Re: Uploading data to SQL server - issue with Date variable

jakarman — Mon, 17 Nov 2014 19:29:24 GMT

Not all all dbms specifics are supported by the sas/access interface. Check: SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Fifth Edition

For SQL-server only native windows drivers exists. Running in Unix you are left to an ODBC connection.

All RDBMS systems are evolving, when you want to know about those details go to that supplier: date (Transact-SQL)

Re: Uploading data to SQL server - issue with Date variable

DangIT — Mon, 17 Nov 2014 19:46:07 GMT

I found a solution that worked in getting around the datetime conversion, its not an elegant solution, but it worked in my case..

I first created an empty table in SQL-sever with the Date variable as a Date format and used a Proc Append from SAS to SQL.

Create TABLE [temp](

[Date] Date ,

[Sales] Real

);

I had to change the Date variable from SAS had to be converted to a text variable with the format 'YYYY-MM-DD'.

Date_txt=put(Year(Date),4.) || "-" || put(Month(Date),z2.) || "-" || put(Day(Date),z2.) ;

Re: Uploading data to SQL server - issue with Date variable

jakarman — Mon, 17 Nov 2014 19:53:09 GMT

Not really a solution as it is telling the date type of sql-server is a string with some constraint on it.

On the other hand first creating that dataset in native settings and than putting the data in is often seen as a good practice.

Re: Uploading data to SQL server - issue with Date variable

Patrick — Mon, 17 Nov 2014 20:28:21 GMT

Creating the table first in a data base instead of using a implicit SAS SQL is something I would call "good practice".

Have you used options "sastrace=",,,d" sastraceloc=saslog nostsuffix;" This would show us in the log the explicit database specific SQL sent to the dbms and we could tell how the SAS/Access engine converts the SAS Date variable and if it does it correctly.

You can also always write explicit SQL as then you have full control of what gets sent. If this needs to be production worthy code then I'd suggest you don't use your current approach with the text string.

Re: Uploading data to SQL server - issue with Date variable

SelvaN — Mon, 17 Nov 2014 20:32:50 GMT

I agree with Reeza and Xia that when SAS dates are converted to SQL Server datetimes and you will see the value of 01/01/1960 with some time on it.

So, either you need to convert the SAS date to date time using dhms or multiplying the date value with date*24*60*60, or create a table in SQL Server explitly as date.

Re: Uploading data to SQL server - issue with Date variable

DangIT — Mon, 17 Nov 2014 21:01:36 GMT

The issue is that I have tables already in SQL server with the Date (no time) format, reading from SQL server to SAS, isn't a problem, but when I write back I have to convert it to a Datetime format, or if i'm appending/replacing a current table, I will get the data type inconsistency error.

The work around I used was to convert the Date to a string and write to the SQL server table with the Date format. In SQL server, the data type for the Date variable is still a Date type and not a string. It seems to write it in as a string from SAS to SQL, but still functions as a Date type when I run queries off of the table.

Re: Uploading data to SQL server - issue with Date variable

jakarman — Mon, 17 Nov 2014 21:18:14 GMT

for the conversion to a string the format yyyymmdd SAS(R) 9.4 Formats and Informats: Reference is better, delivering the wanted string in one approach.
I do not know when the data variable was added in SQL it could be SAS is behind in those interfaces. Just something partially or accidently working. TS?

Re: Uploading data to SQL server - issue with Date variable

DangIT — Mon, 17 Nov 2014 21:27:27 GMT

Thank you for pointing me to the YYMMDDxw. format, i've altered the text conversion code to:

Date_txt=put(Date, YYMMDDd10.) ;

Re: Uploading data to SQL server - issue with Date variable

RXM — Fri, 20 Apr 2018 18:30:51 GMT

Once again you have saved me hours of frustration. Without the time values, valid date formats will not convert properly in SQL (but will not error out).