BookmarkSubscribeRSS Feed
coloric
Calcite | Level 5

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!

11 REPLIES 11
SASKiwi
PROC Star

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
Fluorite | Level 6

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
PROC Star

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
Calcite | Level 5

Thank you!

But now, I got this error:

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

Haikuo
Onyx | Level 15

use DOUBLE quotation mark to let the macro variable resolve:

"&sysdate9:00:00:00"dt

Haikuo

coloric
Calcite | Level 5

Thanks! Smiley Happy

coloric
Calcite | Level 5

And now this error...

ERROR: Date value out of range

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

LarryWorley
Fluorite | Level 6

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
Calcite | Level 5 Hao
Calcite | Level 5

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

rcwright
Calcite | Level 5

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

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

darrylovia
Quartz | Level 8

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;

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!

What is Bayesian Analysis?

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.

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
  • 11 replies
  • 13200 views
  • 0 likes
  • 7 in conversation