Dump dataset to SQL with datetime

[ovel2clock]

I'm trying to dump a SAS dataset to a MS SQL server. The SAS dataset contains a datetime that I can't seem to enter correctly into the database.

proc sql;
insert into mssql.dump_data (date, field1, field2, field3)
select date format=yymmddd10., field1, field2, field3 from local.dataset_model;
quit;

When I run this, the SQL database shows 01JAN1960:04:53:43.000. The dataset show s the date as 01MAR08 and as being a DATE7.

I figured that SAS is returning it's date as a numeric that SQL interprets incorrectly. However, if I tell SAS to return the date as a string, SAS complains and says does not match the data type.

Can anyone help?

Thank you!!

[Cynthia_sas]

Hi: This is not an ODS or Base Reporting procedure question. It is really the kind of question that is best answered by Tech Support. Also remember that there is a HUGE difference between how a simple numeric variable that represents a DATE is stored internally in SAS and how a numeric variable that represents a DATETIME value is stored internally in SAS.

From the documentation topic on
Dates, Times, and Intervals ---> About SAS Date, Time, and Datetime Values, it explains that:

SAS date value
is a value that represents the number of days between January 1, 1960, and a specified date. SAS can perform calculations on dates ranging from A.D. 1582 to A.D. 19,900. Dates before January 1, 1960, are negative numbers; dates after are positive numbers.

SAS datetime value
is a value representing the number of seconds between January 1, 1960 and an hour/minute/second within a specified date.


So, you really have two issues: 1) is the date in your SAS data set a DATE value or a DATETIME value and 2) what is date value in the SQL table in which you are trying to insert the data -- a DATE value or a DATETIME value?? Once you answer both these questions, then you can figure out the right way to do the INSERT.

Tech Support can help you figure out the answer to the questions and the answer to the insert problem.

cynthia

[deleted_user]

Cynthia has given you some excellent advice regarding handling this data. I am going to point you to a couple of other aspects, and hope you will get a full resolution from Tech Support.

The value '01Apr2008'd is a date literal that has a value in SAS internal storage of 17623. This is the number of days since 1Jan1960 as Cynthia explained. The value '04:53:43't has the same internal storage value, and is the number of seconds past midnight. You could have established this with a simple data step to check the values and apply date and time formats. That is the first lesson in debugging unexpected values. Note that the formats don't change the underlying values, they only change the way in which the values are represented. So your statement that the date is reported as Date7 is an artefact of the default format on the table, and is both irrelevant to the actual value of the data, and is something you can change.

What this also instructs us is that the internal value is not being changed in the load to SQL Server, but is being represented incorrectly. This is an important finding since it shows us that we have a data representation issue, not a data corruption issue. As to how to correct the load; I cannot advise you. I don't have Access to SQLServer or ODBC here to test any solution, and this is the sort of problem that needs testing. As Cynthia said, Tech Support is your best bet.

I will comment though that Access, Excel and SQLServer all store datetimes in the same way. The integer part represents the number of days since 1900, so if the values have not suddenly shifted off by 60 years then the load process has recognised the change in the epoch used by the two different systems. That is good news. To hold a time of day, the MS products use decimal parts of days, where 6am is 0.25.

So if your value is going in as a datetime, and coming back as a date or time, then you have a problem in the way in which the data type is represented. I am sure Tech Support will give you clear indications where that is going wrong. I suspect too that careful reading of the manual, or researching of the user papers on support.sas.com would also shed some light.

Kind regards

David