Help using Base SAS procedures

Uploading data to SQL server - issue with Date variable

Reply
Frequent Contributor
Posts: 81

Uploading data to SQL server - issue with Date variable

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.

Super User
Posts: 10,046

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

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

format your_variable date9. ;

Trusted Advisor
Posts: 3,215

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

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.

---->-- ja karman --<-----
Super User
Posts: 19,876

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

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).

Respected Advisor
Posts: 4,173

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

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 ).

Frequent Contributor
Posts: 81

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

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

);

GO

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?

Trusted Advisor
Posts: 3,215

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

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)

---->-- ja karman --<-----
Frequent Contributor
Posts: 81

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

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

);

GO


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.)  ;

Trusted Advisor
Posts: 3,215

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

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. 

---->-- ja karman --<-----
Respected Advisor
Posts: 4,173

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

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.

Respected Advisor
Posts: 4,173

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

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; 

Occasional Contributor
Posts: 9

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

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.

Frequent Contributor
Posts: 81

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

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.

Trusted Advisor
Posts: 3,215

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

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?  

---->-- ja karman --<-----
Frequent Contributor
Posts: 81

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

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

Date_txt=put(Date, YYMMDDd10.) ;

Ask a Question
Discussion stats
  • 14 replies
  • 3940 views
  • 7 likes
  • 6 in conversation