10-05-2017 07:38 AM
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
10-05-2017 09:40 AM
Create an intermediary table in the SQL server and insert there, best by using pass-through SQL.
10-07-2017 02:19 PM
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.
10-08-2017 03:19 PM
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?
10-08-2017 03:34 PM - edited 10-08-2017 03:34 PM
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.
10-13-2017 01:39 AM
I suggest you convert BIRTHDTE from DATE to DATETIME:
birthdte = dhms(birthdte, 0, 0, 0); format birthdte datetime 22.2;
and try inserting that.