topic Re: PROC SQL - Insert date on Oracle table in SAS Procedures

PROC SQL - Insert date on Oracle table

coloric — Wed, 31 Oct 2012 16:57:24 GMT

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!

Re: PROC SQL - Insert date on Oracle table

SASKiwi — Wed, 31 Oct 2012 19:15:54 GMT

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;

Re: PROC SQL - Insert date on Oracle table

LarryWorley — Wed, 31 Oct 2012 20:08:57 GMT

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.

Re: PROC SQL - Insert date on Oracle table

SASKiwi — Wed, 31 Oct 2012 23:04:29 GMT

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

Re: PROC SQL - Insert date on Oracle table

coloric — Thu, 01 Nov 2012 13:28:06 GMT

Thank you!

But now, I got this error:

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

Re: PROC SQL - Insert date on Oracle table

Haikuo — Thu, 01 Nov 2012 13:32:28 GMT

use DOUBLE quotation mark to let the macro variable resolve:

"&sysdate9:00:00:00"dt

Haikuo

Re: PROC SQL - Insert date on Oracle table

coloric — Thu, 01 Nov 2012 13:39:15 GMT

Thanks!

Re: PROC SQL - Insert date on Oracle table

coloric — Thu, 01 Nov 2012 13:47:22 GMT

And now this error...

ERROR: Date value out of range

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

Re: PROC SQL - Insert date on Oracle table

rcwright — Thu, 01 Nov 2012 14:01:31 GMT

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

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

Re: PROC SQL - Insert date on Oracle table

LarryWorley — Thu, 01 Nov 2012 14:11:03 GMT

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.

Re: PROC SQL - Insert date on Oracle table

darrylovia — Thu, 01 Nov 2012 14:27:24 GMT

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;

Re: PROC SQL - Insert date on Oracle table

Hao — Thu, 01 Nov 2012 15:35:43 GMT

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