Date Format Issue when loading SAS into SQL Server using SSIS via OLE DB provider

[KSAS56]

I was able to load a SAS dataset into SQL server using SSIS via OLE DB provider. However the Date column's format in SAS is YYMMDDN8. (Informat: 8.), which shows: 20141209 (for example in FSVIEW), when I load it to a staging table in SQL Server with the column type as DATE, it shows 1954-12-08. (If I export to a txt and load into a staging varchar table and then final table with DATE as date type, the date will be correct, but I would like to save a step and go directly from SAS to SQL Server). Anyone has a solution to this? Thanks!! 

[SASKiwi · Accepted solution]

Most likely the date columns you are trying to load are datetime columns. Try creating a datetime in SAS with a format of datetime22.2. This is what we do when loading SQL Server tables.

[KSAS56]

@SASKiwi , Thanks for your suggestion, however when I tried to create format my_dt datetime22.2, all of the original dates such as:
20141209

20141121

20150121

turn out to be:

01JAN1960:05:34:26.00

01JAN1960:05:34:08.00

01JAN1960:05:35:09.00

So looks like the original columns are date columns, when format using datetime, SAS thinks they are only minutes apart within the same date. One solution that I thought of after your suggestion is: instead of originally thinking I have to export to a TXT in order to maintain the correct date. I tested to just do PUT(my_dt, yymmddn8.) and leave the date column as text and that will actually solve my problem. So instead of TXT file, just a few TXT columns and let SSIS do the transformation. Thank you!

[r_behata]

What is the version of the SQL Server ? Have you tried loading with the SAS/Access to SQL Server ( Needs License)  or ODBC ?

[KSAS56]

@r_behata, SQL Server is v18.0(2019). Yes, we thought about using the ODBC, and since that is not free, we thought we would try the free ole db provider first.

[VDD]

@KSAS56 reminder that SAS stores dates in a numeric value.  

SAS dates and SQL server starting dates are not the same so you have to force SQL server to understand what the correct date is for the value you are passing.

[KSAS56]

@VDD , Thanks for your advice! I understand the starting dates aren't the same. SAS is 1960 Jan 1st, do you know what date is SQL server's start date. I used a date calc app to check, the differences between the 20141209 and 1954-12-08 is 21916 days. If I put below is my sql code:
DATEADD(day, 21916, my_dt) then all the dates are calculated correctly.

But I am still trying to eliminate extra coding and especially if I miss a couple columns then the job won't run.

But thanks again for your help!!! Greatly appreciate it.

[ErikLund_Jensen]

Hi @KSAS56 

I think it should work with a correct date format, so you don't need to cope with the different starting points in SQL and SAS (1900 vs 1960).

But remember that you cannot turn a SAS date into a SAS datetime with a format, because a SAS date value has dates as time unit,  while a SAS datetime value has seconds as unit. So the number of days is changed to a number of seconds, when you use a datetime format on a date value, as this example shows:

189  data _null_;
190      date = '09dec2014'd;
191      put date datetime22.2;
192
193      date = date*24*3600;
194      put date datetime22.2;
195  run;

 01JAN1960:05:34:26.00
 09DEC2014:00:00:00.00

I guess everything works if you convert the number of days to number of seconds.

[Tom]

What is SSIS?

Show the code you used to load the data in the database.

Did you just use PROC APPEND?

libname mydb .... ;
proc append base=mydb.mytable data=mysastable;
run;

[KSAS56]

@Tom, SSIS is SQL Server Integration Service, it's an ETL tool that can store all the SQL stored procedures and can do extra transformations (such as split, merge, fuzzy look up, etc) and can be automated to run all the configured jobs based on your schedule. I didn't use PROC APPEND. I was just using simple create table and insert statement in SQL server.

[Tom]

---

@KSAS56 wrote:
@Tom, SSIS is SQL Server Integration Service, it's an ETL tool that can store all the SQL stored procedures and can do extra transformations (such as split, merge, fuzzy look up, etc) and can be automated to run all the configured jobs based on your schedule. I didn't use PROC APPEND. I was just using simple create table and insert statement in SQL server.

---

So instead of using SAS code to connect to the SQL server and PUSH your data into the database you are using a process on the SQL server to PULL data from SAS via some type of connection?  What type of connection are your using? 

It now sounds like your question is why didn't that process recognize that the variable had a date type format attached to it and automatically convert the values.  I would recommend like others have to try loading from a SAS dataset that has a variable with DATETIME values instead of a DATE values and see if that gets converted properly.  You can use the DHMS() function to convert.  

datetimevar = MHMS(datevar,0,0,0) ;
format datetimevar datetime20.;

[KSAS56]

@Tom, yes. We are doing exactly as you mentioned. "Push" require SAS/Access ODBC driver (license involved, and we will be able to do soon); "Pull" free download using OLE DB Provider (latest 9.4 version) and I have tried the DHMS(my_dt, 0,0,0) function you mentioned and it does produce the right output, I will test next in the SQL Server and see if it achieve what I am looking for, thanks so much!

[JBailey]

Hi @KSAS56 

If I have this right, you are driving this process from SSIS by connecting to SAS via a SAS OLE DB provider?

If this is the case, I think you may have to create a SAS Data step view that converts the SAS date into something that SSIS can understand. This will enable the process to happen in one-fell-swoop; you shouldn't have to load the data into a temporary table then massage it. 

On the other hand, it is much easier to do this with SAS/ACCESS Interface to Microsoft SQL Server or SAS/ACCESS Interface to ODBC.

Best wishes,
Jeff

[KSAS56]

Thank you for your advices. I will try without the temp table and see (I think the process I ended up with temp table was b/c TXT format was what I was first testing and I just inherit this process when I have a new SAS data source. Also we will be getting the license to do the ODBC way soon ($$ commitment).

[VDD]

this issue is a Microsoft problem.  Use SAS to do what it can do its easier than using MS.