topic Re: Use of WHERE and OR in the same proc SQL in SAS Procedures

Use of WHERE and OR in the same proc SQL

altijani — Mon, 30 Mar 2020 12:26:31 GMT

Hello Community -

I have an extremely simple coding question, which I am stuck at:

My current code is (which resulted in an abort to the teradata due to size limitation):

PROC SQL;
CREATE TABLE Short_Table AS
SELECT DISTINCT *

From All_Data

where variable1 = 123

and variable2 = 'xyz' or variable3 = 'abc' /*cause of the error, because it pulls all the data*/

Quit;

I also tried this version:

and (variable2 = 'xyz' or variable3 = 'abc') /* the results did not include the variable3 condition*/

Thank you in advance!

Re: Use of WHERE and OR in the same proc SQL

PaigeMiller — Mon, 30 Mar 2020 12:31:36 GMT

You have told us what doesn't work, but we don't know what output you do want.

Can you show us a small data set as an example, with the desired output?

Re: Use of WHERE and OR in the same proc SQL

Kurt_Bremser — Mon, 30 Mar 2020 12:32:18 GMT

First:

where variable1 = 123 and variable2 = 'xyz' or variable3 = 'abc'

is equivalent to

where (variable1 = 123 and variable2 = 'xyz') or variable3 = 'abc'

as "and" is evaluated before "or".

So your try

where variable1 = 123 and (variable2 = 'xyz' or variable3 = 'abc')

is correct, but depends on the actual contents of variable3. Spelling, uppercase/lowercase, leading blanks can all cause your condition to never be true.

Re: Use of WHERE and OR in the same proc SQL

ballardw — Mon, 30 Mar 2020 15:25:44 GMT

Since you say you had an error that "pulls all the data" then you might want to share some of the value incorrectly pulled and explain why those are incorrect. The values would only have to be from your variables 1 , 2 and 3.

However these may need to be actual values and not your moderately obvious dummy values.