10-31-2012 12:57 PM
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:
INSERT INTO test
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!
10-31-2012 03:15 PM
Something like this should work. You can't use a SAS function inside an SQL VALUES list - check the documentation for what is allowable.
INSERT INTO test
10-31-2012 04:08 PM
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
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.
10-31-2012 07:04 PM
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".
11-01-2012 10:11 AM
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.
11-01-2012 10:27 AM
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
format mydate datetime.;
insert into myora.test1 (det,mydate)