BookmarkSubscribeRSS Feed
itchyeyeballs
Pyrite | Level 9

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

8 REPLIES 8
Ksharp
Super User

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

itchyeyeballs
Pyrite | Level 9

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.

SASKiwi
PROC Star

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? 

itchyeyeballs
Pyrite | Level 9

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. 

SASKiwi
PROC Star

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

 

proc contents data = desktop.table;
run;
itchyeyeballs
Pyrite | Level 9

 

Sample attachedCapture.GIF

SASKiwi
PROC Star

I suggest you convert BIRTHDTE from DATE to DATETIME:

 

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

 and try inserting that.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 2296 views
  • 2 likes
  • 4 in conversation