BookmarkSubscribeRSS Feed
DangIT
Fluorite | Level 6

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.

15 REPLIES 15
Ksharp
Super User

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

format your_variable date9. ;

jakarman
Barite | Level 11

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 --<-----
Reeza
Super User

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

Patrick
Opal | Level 21

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

DangIT
Fluorite | Level 6

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?

jakarman
Barite | Level 11

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 --<-----
DangIT
Fluorite | Level 6

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

jakarman
Barite | Level 11

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 --<-----
Patrick
Opal | Level 21

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.

RXM
Fluorite | Level 6 RXM
Fluorite | Level 6

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

Patrick
Opal | Level 21

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; 

SelvaN
Fluorite | Level 6

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.

DangIT
Fluorite | Level 6

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.

jakarman
Barite | Level 11

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 15 replies
  • 10747 views
  • 8 likes
  • 7 in conversation