topic Re: ORA-01858 error in SAS Procedures

ORA-01858 error

PS — Tue, 19 Jan 2010 14:54:33 GMT

Hi All,
I am a begineer in SAS and trying to create a data set through SAS from a Oracle table. The SQL query is running fine in sql prompt but when I embed the sql in SAS and try to write the o/p, it throws the error below:
"ERROR: ORACLE execute error: ORA-01858: a non-numeric character was found where a numeric was expected"

If I ommit the put command, the program runs fine. Can anyone please help me out.

Thanks,
Premjit

Re: ORA-01858 error

Flip — Tue, 19 Jan 2010 15:06:17 GMT

Since you include no code, and do not specify what the put command is doing I can only assume you are using a numeric format on a character value.
A lot more info would be needed to get specific.

Re: ORA-01858 error

sbb — Tue, 19 Jan 2010 15:19:57 GMT

Also, you may want to search the SAS support http://support.sas.com/ website first - here is a match on the error number/message you referenced.

Useful information also would be your SAS version, the OS where you are running, and whether you (or other individuals) have other SAS code working successfully accessing ORACLE.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search argument on this topic/post:

ora-01858 numeric expected site:sas.com

Re: ORA-01858 error

Flip — Tue, 19 Jan 2010 15:23:12 GMT

The error is Oracle http://www.orafaq.com/wiki/ORA-01858 You are converting an improper date.

Re: ORA-01858 error

PS — Wed, 20 Jan 2010 03:55:59 GMT

Yes I have date feilds but I have converted it to character while selecting e.g to_char(ambs.D_CLSE,'yyyy-mm-dd') .

The put statement is as below:

disconnect from oracle;
quit;
filename out "$DDIR/trmd699b.dat" ;
data _null_;
set v_credit_card;
file out noprint notitles dlm=',' DSD DROPOVER LRECL=32767;
put
N_ACCT
N_CLG
N_CUST_01
N_CUST_02
N_CUST_03
N_CUST_04
N_CUST_05
N_CBS_SCOR_RAW_01
N_CBS_SCOR_RAW_02
N_CBS_SCOR_RAW_03
N_CBS_SCOR_RAW_04
N_CBS_SCOR_RAW_05
N_CBS_SCOR_FINL_01
N_CBS_SCOR_FINL_02
N_CBS_SCOR_FINL_03
N_CBS_SCOR_FINL_04
N_CBS_SCOR_FINL_05
------------------------------------------
------------------------------------------

run;

Re: ORA-01858 error

PS — Wed, 20 Jan 2010 07:45:31 GMT

Is there a way by which I can force SAS to treat all variables as character instead of number. I basically want to override SAS's approcah of treating everything as NUMBER

Re: ORA-01858 error

PS — Wed, 20 Jan 2010 09:15:20 GMT

Guys I have found out the problem and fixed it. If anyone face this problem again,
please bear in mind that Oracle date and SAS date formats are not at all same. You have to explicitly give the date format in your select query written in proc sql.
eg. The following statement
select 'a' from dual where sysdate='21/JAN/2010' will work in sql prompt.

But if you embed it in SAS there is a problem. You have to modify this statement into
select 'a' from dual where sysdate= to_date('21/JAN/2010','dd/mon/yyyy')

thanks everyone ...