Desktop productivity for business analysts and programmers

Proc SQL inserting dates issue

Reply
Frequent Contributor
Posts: 115

Proc SQL inserting dates issue

Hi All,

 

Having some trouble with dates Smiley Sad

 

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

 

Any ideas, database is MS sql Server

Super User
Posts: 7,422

Re: Proc SQL inserting dates issue

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

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 9,867

Re: Proc SQL inserting dates issue

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

Frequent Contributor
Posts: 115

Re: Proc SQL inserting dates issue

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.

Super User
Posts: 3,233

Re: Proc SQL inserting dates issue

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? 

Frequent Contributor
Posts: 115

Re: Proc SQL inserting dates issue

[ Edited ]

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. 

Super User
Posts: 3,233

Re: Proc SQL inserting dates issue

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

 

proc contents data = desktop.table;
run;
Frequent Contributor
Posts: 115

Re: Proc SQL inserting dates issue

 

Sample attachedCapture.GIF

Super User
Posts: 3,233

Re: Proc SQL inserting dates issue

I suggest you convert BIRTHDTE from DATE to DATETIME:

 

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

 and try inserting that.

Ask a Question
Discussion stats
  • 8 replies
  • 194 views
  • 2 likes
  • 4 in conversation