Question about SAS 9.4 implicitly ignoring character value passed to numeric variable

[Chandu91]

Hi,

I have come across a different set of coding as we can pass both numeric and character values at a time to a variable that was declared as numeric in Oracle warehouse.

Example :-

proc sql;
connect to oracle (XXXXXXXXXXXXXX user=&uid. password=&pwd. preserve_comments );
    create table temp as
        select * from connection to oracle
(select Var from CC
where var in ('023',23);

);
quit;

It is working but could someone help me to understand how the SAS is not throwing an error when there are two format values given in a condition?

If system is considering '023' as numeric and not a character even with quote this should work in local sas code too. but this is working only when we are hitting oracle data warehouse.

data cc;
input var;
cards;
23
91
65
;
run;

proc sql;
create table temp as 
select * from cc
where var in ('023',23);
quit

Thanks

[Tom]

I don't get the premise of your question.

You showed an Oracle query.  Then you showed a SAS query using a totally different WHERE clause.

Did you try the same where clause you used when querying the SAS dataset in your Oracle query?

What makes you think that the variable is numeric in the Oracle table?  The query you sent to Oracle is treating it as character.

select Var from CC where var in ('023')

[Chandu91]

Yeah i have updated the query in the previous post. My question is you can't pass two format values to a numeric defined variable.

If you try to o=do that SAS system should throw an error saying check your format which is exactly done in the second part of my question.

But the system is ignoring the format issue when you are hitting Oracle warehouse. I'm trying to understand why there is no error or warning message in the log.

[Tom]

---

@Chandu91 wrote:

Yeah i have updated the query in the previous post. My question is you can't pass two format values to a numeric defined variable.

If you try to o=do that SAS system should throw an error saying check your format which is exactly done in the second part of my question.

But the system is ignoring the format issue when you are hitting Oracle warehouse. I'm trying to understand why there is no error or warning message in the log.

---

Note that in SAS a FORMAT is an attribute of a variable that tells SAS how to display the values.  I think you are actually asking about type conversion. SAS only has two types of variables, floating point numbers and fixed length character strings. (DS2 allows more).

Let me make sure I understand the question.  You updated the question and it looks like you are saying that Oracle will allow you to use a string like '023' that LOOKS like a number to query a variable that is a number.  SAS will not allow that with the WHERE statement.  But you can do that in many places in normal SAS code however.  Like in a IF statement instead.

1     data test;
2       set sashelp.class;
3       where age in (13,'14');
                         ----
                         22
                         200
ERROR: Syntax error while parsing WHERE clause.
ERROR 22-322: Syntax error, expecting one of the following: a numeric constant, a datetime constant,
              a missing value, ), -.

ERROR 200-322: The symbol is not recognized and will be ignored.

4     run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.TEST may be incomplete.  When this step was stopped there were 0 observations
         and 5 variables.

5
6     data test;
7       set sashelp.class;
8       if age in (13,'14');
ERROR: All variables in array list must be the same type, i.e., all numeric or character.
9     run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.TEST may be incomplete.  When this step was stopped there were 0 observations
         and 5 variables.
WARNING: Data set WORK.TEST was not replaced because this step was stopped.

10    data test;
11      set sashelp.class;
12      if age in ('13','14');
13    run;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
      12:6
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.TEST has 7 observations and 5 variables.

14
15    data test;
16      set sashelp.class;
17      if age = 13 or age='14';
18    run;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
      17:22
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.TEST has 7 observations and 5 variables.

[Panagiotis]

proc sql;
connect to oracle (XXXXXXXXXXXXXX user=&uid. password=&pwd. preserve_comments );
    create table temp as
        select * from connection to oracle
(select Var from CC
where var in ('023');

);
quit;

In this example you are using explicit pass through. Which means you are sending that query

(select Var from CC where var in ('023'))

directly to Oracle for processing. Oracle is processing the query as written.

I have come across a different set of coding as we can pass both numeric and character values at a time to a variable that was declared as numeric in Oracle warehouse.

You are also only testing var against a character value, not both like you mentioned. If i'm understanding correctly.

When I try the same thing using a numeric column and testing for a character value I receive an error. Are you sure VAR is numeric?

/*MSRP is a numeric column*/
proc sql;
connect to oracle(user=&username password=&pwd path=&path);
select * from connection to oracle
	(select * 
		from cars
		where MSRP in ('test'));
disconnect from oracle;
quit;

Also if you look at my error, that is an error from Oracle.

When I try taking a numeric column and testing for both a character and numeric value, I again receive an error.

proc sql;
connect to oracle(user=&username password=&pwd path=&path);
select * from connection to oracle
	(select * 
		from cars
		where MSRP in ('test', 23820));
disconnect from oracle;
quit;

I'm guessing if you change this

(select Var from CC where var in ('023'));

To this

(select Var from CC where var in ('023', 212));

You should receive an error.  

[Chandu91]

"I'm guessing if you change this

(select Var from CC where var in ('023'));

To this

(select Var from CC where var in ('023', 212));

You should receive an error.  "

Exactly, that is my point too, but the twist in the tale is i'm not getting any ERROR or not even a Warning message in the log.

[Panagiotis]

Hm. I'd guess you should receive an error, I know I did. Could you show us the log after you run:

proc sql;
connect to oracle (XXXXXXXXXXXXXX user=&uid. password=&pwd. preserve_comments );
    create table temp as
        select * from connection to oracle
(select Var from CC
where var in ('023',23);

);
quit;

And can you confirm the data type of the VAR column in Oracle.

- Peter

[Chandu91]

here the log :-

59 OPTIONS OBS=1;
60 proc sql;
61 connect to oracle (path="XXXXXX" BUFFSIZE=5000 user=&uid. password=&pwd. preserve_comments
61 ! );
62 create table temp as
63 select * from connection to oracle
64 (select Var
66 from CC
67 where Var in ('023',23)
68
69 );
NOTE: Table WORK.TEMP created, with 1 rows and 1 columns.

70 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 5.00 seconds
cpu time 0.04 seconds

NOTE: Remote submit to GRID complete.
NOTE: Remote submit to PCSRV complete.

[Patrick]

Looks like Oracle does implicit data type conversion like documented here. 

[Chandu91]

Maybe correct me if i'm wrong, But the 2-10 Matrix says like this :-

Char        > Char

Char        > Rowid

Varchar2  > Varchar 2

Number   > number

Number   > date

Number   > date time

Number   > log .. etc

but not converting from Char to Numeric/ Numeric to Char, which says oracle it's not implicitly converting.

Is there any possibility that the datatype of the variable in Oracle is defined as VARCHAR and while fetching information with SAS tool it's converting to numeric and bypassing to check the format of values?

[Patrick · Accepted solution]

The IN clause executes fully on the Oracle side without any SAS involvement. The only thing SAS does is sending the code to Oracle for execution. Therefore your question is 100% Oracle related and has nothing to do with SAS. 

[Chandu91]

the datatype of Var column is Numeric 8.

[Tom]

---

@Chandu91 wrote:

"I'm guessing if you change this

(select Var from CC where var in ('023'));

To this

(select Var from CC where var in ('023', 212));

You should receive an error.  "

Exactly, that is my point too, but the twist in the tale is i'm not getting any ERROR or not even a Warning message in the log.

---

You need to raise questions about how Oracle works on an Oracle Forum, not a SAS forum.