Help using Base SAS procedures


Frequent Contributor
Posts: 131


I am using PROC Sql Pass-through facility in SAS by connecting to Oracle.

I am having the problem when I run the query on UNIX.

Query works fine on Windows platform My query has following lines in the where statement that causes the errorSmiley SadRec_date has a datetime format)

proc sql;

connect to oracle(user=xxx orapw="xyz" path="abc"); 

create table test_tbl as

select *

from connection to oracle  ( 

select *    from test

where trunc(x.rec_date) between trunc(nvl(a.eff_dt, '01-JAN-1900')) and trunc(nvl(b.end_dt, '31-DEC-9999')) );


ERROR: ORACLE execute error: ORA-01858: a non-numeric character was found where a numeric was expected.

I would appreciate any help in this..

Posts: 72

Re: SAS/SQL Error

The error is of Oracle with the error code 01858.

This error can be because the data types of your columns are not date.

Also I don't see any unit of measure with your truncate function as well. Are you trying to truncate date-time field to date?

Also, is it the same query you are trying to run?

Because I don’t see any alias name for the tables referred as 'a' , 'b' and 'x'. And if it is just an example and rec_date is date-time format then try nvl function with a date-time replacement, such as '31-dec-9999:00:00:00'.

It would be helpful if you can provide your requirement.

Trusted Advisor
Posts: 3,215

Re: SAS/SQL Error

The nvl is an oracle function and you are getting an oracle error. Something is wrong with Oracle.  Most likey the input data is not as expected with x.rec_date an/or b.end_dt.

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 2 replies
  • 3 in conversation