topic Proc SQL inserting dates issue in SAS Enterprise Guide

Proc SQL inserting dates issue

itchyeyeballs — Thu, 05 Oct 2017 11:38:09 GMT

Hi All,

Having some trouble with dates 😞

I'm trying to update a database table but the dates keep getting scrambled.

my dataset has dates in the format IS8601DA10

I created a table using

PROC SQL;
CREATE TABLE MIWrite.table5
AS SELECT * FROM desktop.table;
QUIT;

this creates datetimes in my database but seems to work as the dateelement is correct so I was happy.

However when I try to insert new values using

Proc SQL;
INSERT INTO MIWrite.Table5
SELECT 	*
FROM Desktop.table;
QUIT;

All the new dates show along the lines of 1959-12-31 23:50:41.000

I have tried to change the formats of the columns in both the database structure and via SAS code but not able to resolve.

I cant figure out why it works when creating but not when updating 😞

Any ideas, database is MS sql Server

Re: Proc SQL inserting dates issue

Kurt_Bremser — Thu, 05 Oct 2017 13:40:31 GMT

Create an intermediary table in the SQL server and insert there, best by using pass-through SQL.

Re: Proc SQL inserting dates issue

Ksharp — Thu, 05 Oct 2017 14:43:11 GMT

Try to format the datetime variables in Desktop.table with datetime32. format.

Re: Proc SQL inserting dates issue

itchyeyeballs — Sat, 07 Oct 2017 18:19:12 GMT

Hi guys,

Thanks for the tips, I tried to play with both but struggled to get it working, ended up using "put" to covert to text format which is not ideal but better than where I was.

I still can't understand why creating the table using the SAS data set worked fine but then inserting from the same data set (after refreshing) fell over, guess its some kind of SAS vs MS interoperability thing.

Re: Proc SQL inserting dates issue

SASKiwi — Sun, 08 Oct 2017 19:19:18 GMT

I do this myself a lot and as long the date variable is defined as a SAS DATETIME with a format of DATETIME22.2 then I've not had any problems. How is your date variable defined in your SAS table?

Re: Proc SQL inserting dates issue

itchyeyeballs — Sun, 08 Oct 2017 19:34:49 GMT

Hi @SASKiwi,

The initial data source is an xml file, my map file has (as an example - mostly was auto generated)

        <COLUMN name="BIRTHDTE">
            <PATH syntax="XPath">/StaffRecord/Institution/Person/BIRTHDTE</PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>date</DATATYPE>
            <FORMAT width="10">IS8601DA</FORMAT>
            <INFORMAT width="10">IS8601DA</INFORMAT>
        </COLUMN>

Not sure why but running the create table statement using that source produces a date/time output in my sql server database.

Re: Proc SQL inserting dates issue

SASKiwi — Sun, 08 Oct 2017 19:53:58 GMT

Please run this and post the output. This will describe how your SAS data is defined:

proc contents data = desktop.table;
run;

Re: Proc SQL inserting dates issue

itchyeyeballs — Sun, 08 Oct 2017 20:16:40 GMT

Sample attached

Re: Proc SQL inserting dates issue

SASKiwi — Fri, 13 Oct 2017 05:39:58 GMT

I suggest you convert BIRTHDTE from DATE to DATETIME:

birthdte = dhms(birthdte, 0, 0, 0);
format birthdte datetime 22.2;

and try inserting that.