topic Re: Loading SAS dataset to SQL Server with Date Fields in SAS Programming

Loading SAS dataset to SQL Server with Date Fields

PhatRam33 — Wed, 27 Oct 2021 15:18:51 GMT

I'm having an odd issue. I'm creating a new table in SQL server from SAS for one of my data sets. The date fields are saved in date9 format from original source. When this data gets uploaded to SQL, it is storing as a text field in YYYY-MM-DD for some reason and can't figure out why. Any suggestion or workarounds for this issue?

Re: Loading SAS dataset to SQL Server with Date Fields

Reeza — Wed, 27 Oct 2021 16:15:10 GMT

What type of DB? Usually they require dates to be uploaded as a datetime not a SAS date.

Re: Loading SAS dataset to SQL Server with Date Fields

PhatRam33 — Wed, 27 Oct 2021 16:50:19 GMT

It is a standard Microsoft SQL Server database.

Re: Loading SAS dataset to SQL Server with Date Fields

Reeza — Wed, 27 Oct 2021 16:54:10 GMT

Try using a YYYY-MM-DD format on the SAS side which SQL would recognize as a date literal or the datetime format would be my suggestions then.

Re: Loading SAS dataset to SQL Server with Date Fields

SASKiwi — Wed, 27 Oct 2021 20:50:46 GMT

What SAS SQL Server database engine are you using? SQLSVR or ODBC? What version of MS SQL Server ODBC driver are you using? Older ODBC drivers sometimes have problems handling dates correctly.

Re: Loading SAS dataset to SQL Server with Date Fields

Patrick — Wed, 27 Oct 2021 22:11:19 GMT

If you're really sure that on the SAS side the variable is numerical and contains a SAS Date value then this is really an odd one.

With SAS not having a Date type the only way to detect date variables is via the format applied to the variable. I've experienced in the past that this didn't work properly with all SAS Date formats but it did always work when using formats DATE9 or DATETIME20. (...just the ones SAS creates when loading a DATE type from a DB into SAS).

May be run a proc contents against your source table to ensure the variable is of the correct type with the necessary format attached.

Also use OPTIONS SASTRACE=',,,d' SASTRACELOC=SASLOG NOSTSUFFIX; on top of the code to get a bit more information what's actually happening.

What you describe sounds like the process would have to identify the SAS column as containing a date but then create magically some numeric to text conversion logic (put on the SAS side or cast on the DB side). That would be really odd. Hopefully running your code with above options will tell us a bit more.