BookmarkSubscribeRSS Feed
PS
Calcite | Level 5 PS
Calcite | Level 5
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
6 REPLIES 6
Flip
Fluorite | Level 6
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.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Flip
Fluorite | Level 6
The error is Oracle http://www.orafaq.com/wiki/ORA-01858 You are converting an improper date.
PS
Calcite | Level 5 PS
Calcite | Level 5
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;
PS
Calcite | Level 5 PS
Calcite | Level 5
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
PS
Calcite | Level 5 PS
Calcite | Level 5
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 ...

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
  • 6 replies
  • 2300 views
  • 0 likes
  • 3 in conversation