Proc SQL gives strange numeric error (extracted from Oracle DB)

-_- — Fri, 08 Mar 2019 15:28:28 GMT

Hi!

Given the incompleteness of the description below, I am rather looking for your ideas of what might be wrong, rather than a full solution.

Situation:

-- X64_SRV12 WIN 6.2.9200 Server

-- Oracle database

-- SAS 9.4.3.0, SAS EG 7.12

We have a variable called "nosig" that is an integer between 0 and 400.

I have a very simple SAS PROC SQL query that extracts the "nosig" from the Oracle database.

In the subsequent data step, I filter on nosig outside the above range, i.e. below 0 or above 400.

This dataset should always be empty, as no such nosigs are expected and we know no such data are in the Oracle database.

I run this SAME IDENTICAL query 10 times.

Usually, I get very strange results in at least one of these 10 runs (see attached pictures).

Sometimes, the ten runs produce correct results (i.e. no strange "nosig" values).

Sometimes, up to 7 runs ouf of 10 produce incorrect results.

But I repeat, the query is always the same, the underlying data have not changed between the iterations, and the database actually does not include any of the shown incorrect nosig values.

The wrong nosig numbers are usually either extremely small or extremely high.

There are often hundreds of thousands of rows with incorrect nosigs, but only a handful of unique incorrect values (see pictures).

When the same query is run directly in the SQL, without passing through SAS proc SQL, no such error ever occurres.

Given this information, does this suggest it is a problem of Oracle client, or of the SAS driver ODBC?

How can I possibly find out what the problem is?

Any ideas why the results vary between runs?

Many thanks in advance.

Re: Proc SQL gives strange numeric error (extracted from Oracle DB)

ballardw — Fri, 08 Mar 2019 15:35:48 GMT

It would likely be very helpful to include the LOG results from the connection and the proc sql including the code sections and any notes, messages, warnings or errors.

Edit connection passwords and such but any other option text should be included. Do NOT post a picture of the Log result. Copy from the log and paste into a code box opened using the forum's {I} icon to preserve layout from the log. The main message windows here will reformat text, sometimes making code very hard to read.

The text is easier to copy/paste for pointing out issues and can be highlighted or syntax suggestions made easier. I know that I am not interested in retyping lots of code to point out one small suggestion.

topic Proc SQL gives strange numeric error (extracted from Oracle DB) in New SAS User

Proc SQL gives strange numeric error (extracted from Oracle DB)

Re: Proc SQL gives strange numeric error (extracted from Oracle DB)