Hi!
Please, how to insert current date via INSERT in a oracle table by SAS libname?
This column is a date (data type, but have hours too) on oracle, and date9. (format and informat) on SAS.
Im trying this:
proc sql;
INSERT INTO test
(DET,DT_F,DT_I,ID_J,NM_J,QT_R,STAT,ST)
VALUES(0,'',date(),8,'asdfghjkl',0,'qwertyuiop','zxcvbnm');
quit;
But I got this error on "date()":
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant,
a missing value, ), +, ',', -, MISSING, NULL, USER.
ERROR 200-322: The symbol is not recognized and will be ignored.
I need to insert current datetime in this field.
Thanks a lot!
Something like this should work. You can't use a SAS function inside an SQL VALUES list - check the documentation for what is allowable.
proc sql;
INSERT INTO test
(DET,DT_F,DT_I,ID_J,NM_J,QT_R,STAT,ST)
VALUES(0,'',"&sysdate9:00:00:00"dt,8,'asdfghjkl',0,'qwertyuiop','zxcvbnm');
quit;
Kiwi,
Where can you find this documented on the SAS site? I find the following in the procedures guide for sql insert statement:
INSERT INTO table-name|sas/access-view|proc-sql-view <(column<, ... column>)>
VALUES (value <, ... value>) <... VALUES (value <, ... value>)>; |
Later on the same page, it defines value as
value
is a data value.
So it seems that value must be interpreted as a literal or macro variable which is expanded to a literal before the code is generated. Is that your interpretation?
No where can I find a statement on the SAS site specifically saying sas functions are not allowed in Values Clause. This might be an opportunity for documentation improvement.
A data value to me is either a numeric or character constant: 12345 or '12345' or a special sas constant like '01Jan2012'd for dates or datetimes like '01jan2012:00:00:00'dt. Of course you can also use macro variables as long as they resolve to a valid SAS constant. The problem with SAS documentation is knowing where to look! A SAS function is definitely not a data value and SAS documentation will usually tell you if you can put something like a function that may resolve to a constant by using the word "expression".
Thank you!
But now, I got this error:
ERROR: Invalid date/time/datetime constant '&sysdate9:00:00:00'dt.
use DOUBLE quotation mark to let the macro variable resolve:
"&sysdate9:00:00:00"dt
Haikuo
Thanks!
And now this error...
ERROR: Date value out of range
The column is a DATE datatype... storing date and time.
try changing "&sysdate9:00:00:00"dt to "&sysdate9"d
The table column is a date value, so you need to pass a date literal within the values list.
if use macro variable add one "." after &sysdate9 "&sysdate9.:00:00:00"dt
Is it possible you're confusing date and datetime values?
Try this: Values(0,'',DateTime(),8,'asdfghjkl',0,'qwertyuiop','zxcvbnm');
A few things.
the value clause only accepts values not expressions/functions such as datetime. To get what you want I would create temporary SAS data set then insert that dataset into your oracle table. See my example below
data one;
det=1;
mydate=datetime();
format mydate datetime.;
run;
proc sql;
insert into myora.test1 (det,mydate)
select det,mydate
from one;
quit;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.