I have a dataset (physical SAS table) on our SAS server. I want to write and store the table in a SQL library.
The dataset contains several columns, including a datetime field. For some reason i'm getting the error below.
When I only insert the first row, it works. So i thought it would be the number of decimals or something...
Error: ERROR: During insert: [SAS][ODBC SQL Server Wire Protocol driver]Datetime field overflow. Error in parameter 2. : [SAS][ODBC SQL
Server Wire Protocol driver]Operation cancelled. Error in parameter 2.
Sample data:
ALARM_ID ALARM_INITIAL_TIME (format datetime 18.) ALARM_INITIAL_TIME1 (numeric representation of the INITIAL_TIME)
K0820d7daALRM 24DEC18:00:02:09 1861228929.452
K0820dc61ALRM 24DEC18:00:00:02 1861228802.38189
K0820dc62ALRM 24DEC18:00:00:02 1861228802.35839
K0820dc63ALRM 24DEC18:00:00:02 1861228802.42266
K0820dc68ALRM 24DEC18:00:00:17 1861228817
K0820dc69ALRM 24DEC18:00:00:19 1861228819.032
K0820dc6aALRM 24DEC18:00:00:21 1861228820.50327
K0820dc6bALRM 24DEC18:00:00:23 1861228823
K0820dc6cALRM 24DEC18:00:00:23 1861228823
Any ideas?
Show us the SASLOG for this entire section of code, the entire PROC SQL. Click on the {i} icon and paste that part of the SASLOG into the window that appears.
Hello,
you can not apply a standard date format directly against a datetime value, there are some date formats you can prefix with 'DT' which will display a datetime as a date. Unfortunately the MMDDYY format is not one of these.
https://docs.microsoft.com/en-us/sql/t-sql/functions/sysdatetime-transact-sql?view=sql-server-2017
I just want to keep it as a datetime, not to change it in a date in SQL.
Does SQL not allow my format?
Hi,
No, because you should apply datetime format 'DATETIME18.'
you applied datetime.
that is why your datetime column is overflow.
I have created small example for you. I hope you it goes well.
data test;
input x datetime.;
format x datetime18.;
cards;
25DEC18:00:02:09
;
run;
proc sql;
insert into test values ('26DEC18:00:02:09'dt);
quit;
proc print data=work.test;
format x datetime18.;
run;
I also used one PROC SQL. so, it's 18 length of datetime.
Thanks
--
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.