topic Re: proc SQL where statement - or operators in SAS Programming

proc SQL where statement - or operators

kegallag — Fri, 28 Aug 2020 21:57:22 GMT

In PROC SQL I'd like to set up this conditional statement:

where (not missing (var1) and not missing (var2))

OR upcase (var2) contains 'text'

Does the where clause not allow the "OR" operator?

Re: proc SQL where statement - or operators

PaigeMiller — Fri, 28 Aug 2020 22:01:48 GMT

OR is allowed in PROC SQL in the WHERE clause

What is the problem you are having? Can you show us the LOG of the entire PROC SQL step?

Re: proc SQL where statement - or operators

ballardw — Fri, 28 Aug 2020 22:10:51 GMT

Yes the WHERE clause will accept OR:

proc sql;
   create table junk as
   select * 
   from sashelp.class
   where name='John' or name='Alice'
   ;
quit;

/* try*/
where (not missing (var1) and not missing (var2))
     OR ( upcase (var2) contains 'TEXT' )

make sure that you compare upcased variables to upper case text and sometimes you need to pay attention to

You might want to show your log from when you ran the code. Copy the entire proc sql code along with note, warning or messages from the LOG window and paste into a code box opened on the forum with the </> to prevent the message window from reformatting stuff.

You may also have to provide examples of where you think the code is not working properly, as in values of the variables.

Re: proc SQL where statement - or operators

kegallag — Fri, 28 Aug 2020 22:12:57 GMT

proc sql;
create table X as

select ....

from ....

where (Symptomatic not in ("","Unknown") AND _illness_onset_dt ne ' ') OR upcase (INV_PROCESS_STATUS) contains 'COMPLETE'

and c.case = s.case_no

quit;

ERROR: Expression using not equals (^=) has components that are of different data types.

Re: proc SQL where statement - or operators

kegallag — Fri, 28 Aug 2020 22:18:14 GMT

Thanks! I pasted the code snippet above. Essentially, I'm trying to evaluate with a WHERE statement in PROC SQL whether is a null in both (variable1 *AND* variable2) OR find a string in variable3).

Re: proc SQL where statement - or operators

PaigeMiller — Fri, 28 Aug 2020 22:28:31 GMT

@kegallag wrote:

proc sql;
create table X as

select ....

from ....

where (Symptomatic not in ("","Unknown") AND _illness_onset_dt ne ' ') OR upcase (INV_PROCESS_STATUS) contains 'COMPLETE'

and c.case = s.case_no

quit;

ERROR: Expression using not equals (^=) has components that are of different data types.

Please show us the LOG, all of it, with nothing chopped out, for this PROC SQL step.

Re: proc SQL where statement - or operators

kegallag — Fri, 28 Aug 2020 22:31:49 GMT

11811 proc sql;
11812 create table nmedss_complete_inv as
11813 select c.INV_PROCESS_STATUS as status, c.UPDATED_OUTCOME as outcome,
11813! c.MONITORING_PROCESS as process,
11814 c._INV_START_DT as investStartDate format mmddyy10., c._patient_id as
11814! nmedss_id, c.INV_LOCAL_ID, c.PATIENT_LAST_NAME as lastname, c.PATIENT_FIRST_NAME as
11814! firstname,
11815 c._patient_dob as dob, c.PATIENT_CURRENT_SEX as sex, c.PATIENT_STREET_ADDR_1 as
11815! address1, c.patient_city as city, c._combined_county as county,
11816 s.state_name as state, c.JURISDICTION_NM as jurisdiction, c.PATIENT_TEL_HOME as
11816! home_phone, c.PATIENT_TEL_CELL as cell_phone,
11817 c._nm_race_ethnicity as ethnicity, c.Tribal_affiliation, c.PHC_INV_LAST_NAME as
11817! investigator, c.ORDERING_FACILITY as orderfac, c.ltc_facility, c.ltc_status
11818 from covid19_complete c, states_fips s
11819
11820 where Symptomatic not in ("","Unknown") and _illness_onset_dt ne '' or upcase
11820! (INV_PROCESS_STATUS) contains 'COMPLETE'
11821 and c.patient_state = s.fips_code
11822 and JURISDICTION_NM not in ("NWTribal","Tribal","Federal Facility", "ICE Facility",
11822! "New Mexico Corrections Department")
11823 and _detention_facility=''
11824 and upcase(ltc_status) not in ('RESIDENT', 'UNKNOWN')
11825 and (upcase(PHC_INV_LAST_NAME) like '%CENTRAL%' or upcase(PHC_INV_LAST_NAME) like
11825! '%BAUMBACH%' or upcase(PHC_INV_LAST_NAME) like '%EKPE%'
11826 or upcase(PHC_INV_LAST_NAME) like '%FITZPATRICK%' or upcase(PHC_INV_LAST_NAME)
11826! like '%GARCIA-LOPEZ%'
11827 or upcase(PHC_INV_LAST_NAME) like '%ADMINISTRATIVE OFFICE%' or
11827! upcase(PHC_INV_LAST_NAME) like '%LAS CRUCES HEALTH%'
11828 or upcase(PHC_INV_LAST_NAME) like '%GALLUP%')
11829 and (upcase(orderfac) not like '%NAVAJO-%' and upcase(orderfac) not like
11829! '%CROWNPOINT%' and upcase(orderfac) not like '%CHINLE%'
11830 and upcase(orderfac) not like '%INDIAN HEALTH CENTER%' and upcase(orderfac) not
11830! like '%NORTHERN NAVAJO MED%' and upcase(orderfac) not like
11831 '%INDIAN HOSPITAL %' and upcase(orderfac) not like '%ZIA HEALTH %' and
11831! upcase(orderfac) not like '%AIH CLINICAL %' and
11832 upcase(orderfac) not like '%PUEBLO%');ERROR: Expression using not equals (^=) has components that are of different data types.
11833 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 14.17 seconds
cpu time 0.45 seconds

Re: proc SQL where statement - or operators

PaigeMiller — Fri, 28 Aug 2020 23:14:08 GMT

ERROR: Expression using not equals (^=) has components that are of different data types.

Somewhere in your code, you are testing if a numeric variable (or value) is not equal to a character variable (or value), or vice versa. SAS does not allow such a comparison.

So your task now is look at each place where you are comparing two variables (or a variable and a value) and determine where the mismatch is.

Re: proc SQL where statement - or operators

SASKiwi — Fri, 28 Aug 2020 23:21:24 GMT

Somewhere in your WHERE condition you are either comparing a character column with a numeric constant or more likely a numeric column with a character constant (one enclosed in quotes). You need to check the type of all of the columns you've used in your WHERE - they will be either character or numeric - to identify which one(s) have a type mismatch.

Re: proc SQL where statement - or operators

mkeintz — Sat, 29 Aug 2020 00:49:10 GMT

You where condition includes the test for

_illness_onset_dt ne ' '

If _illness_onset_dt is a date value, it must be numeric, and should not be compared to a character value. That means you should replace the ' ' with a dot, as in

_illness_onset_dt ne .