PROC SQL - Insert date on Oracle table

[coloric]

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!

[SASKiwi]

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;

[LarryWorley]

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.

[SASKiwi]

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".

[coloric]

Thank you!

But now, I got this error:

ERROR: Invalid date/time/datetime constant '&sysdate9:00:00:00'dt.

[Haikuo]

use DOUBLE quotation mark to let the macro variable resolve:

"&sysdate9:00:00:00"dt

Haikuo

[coloric]

Thanks!

[coloric]

And now this error...

ERROR: Date value out of range

The column is a DATE datatype... storing date and time.

[LarryWorley]

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.

[Hao]

if use macro variable add one "." after &sysdate9  "&sysdate9.:00:00:00"dt

[rcwright]

Is it possible you're confusing date and datetime values?

Try this: Values(0,'',DateTime(),8,'asdfghjkl',0,'qwertyuiop','zxcvbnm');

[darrylovia]

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;