topic ACCESS/Hadoop, PROC SQL, WHERE statement in SAS Programming

ACCESS/Hadoop, PROC SQL, WHERE statement

shoin — Mon, 21 Jan 2019 20:10:18 GMT

(Hortonworks Hadoop accessed via SAS ACCESS/Hadoop, a Kerberos enviornment)

Are there any limitation using WHERE predicate in a PROC SQL statement.

Example:

proc sql;

create table a as select *

from hdp_tb.transact x

where x.order < 100 ;

I only get 0 obs.

If I break it

proc sql;

create table a as select from hdp_tb.transact x

;

quit;

proc sql ;

create table b as select from a

where x.order < 100

;

quit;

I get obs, and vars returned.

What am i doing wrong or is there a limitation? Is my construct in-correct?

Thank you for your input

Re: ACCESS/Hadoop, PROC SQL, WHERE statement

Tom — Mon, 21 Jan 2019 20:31:05 GMT

Not really clear what you are doing since your last query is referencing X, but doesn't define X.

One thing to note is the difference in how other systems handle missing values (what they refer to as NULL values).

In SAS all comparisons, like ORDER < 100, use binary logic. They are either TRUE or FALSE. To make this possible SAS treats missing numeric values is less than any valid numeric value. So the test (. < 100) is TRUE. And the test (. > 100) is FALSE.

But it other SQL implementations they use essentially tri-level logic. The result can either be TRUE, FALSE or unknown. So (null < 100) not TRUE.

So perhaps your data has missing values for ORDER.

Try

 where (x.order < 100) or (x.order is null)

Re: ACCESS/Hadoop, PROC SQL, WHERE statement

shoin — Mon, 21 Jan 2019 21:40:10 GMT

Thank you, the x.order (as you pointed out) is a typo. I did not catch it and stand corrected.

The question is that the first sql where predicate brings 0 obs and in the second attempt; the 2 sql statements (for a moment ignore x.order and assume it is order < 100) it does bring back results. I am only trying to find out as to why would that be.

TIA