topic Re: WHERE clause operator requires compatible variables Error in SAS Programming

WHERE clause operator requires compatible variables Error

mayasak — Wed, 06 Jan 2021 13:16:42 GMT

Hi,

I have this SAS code that I need to use so I can have the snomed codes to cover all the types of result texts that I have in excel sheet.

if test_loinc="94558-4" then do; /*Note: this is an antigen test*/
result_snomed=10828004;
where (test_result="Positive"
or test_result="POSITIVE"
or test_result="positive");
result_snomed=260373001;
where (test_result="Detected"
or test_result="DETECTED"
or test_result="detected");
result_snomed=260385009;
where (test_result="Negative"
or test_result="NEGATIVE"
or test_result="negative");
result_snomed=260415000;
where (test_result="Not Detected"
or test_result="NOT DETECTED"
or test_result="not detected"
or test_result="Not detected");
end;
else if test_loinc="94759-8" then do; /*Note: this is a PCR test*/
result_snomed=10828004;
where (test_result="Positive"
or test_result="POSITIVE"
or test_result="positive");
result_snomed=260373001;
where (test_result="Detected"
or test_result="DETECTED"
or test_result="detected");
result_snomed=125154007;
where (test_result="Unsatisfactory"
or test_result="UNSATISFACTORY"
or test_result="unsatisfactory");
result_snomed=260385009;
where (test_result="Negative"
or test_result="NEGATIVE"
or test_result="negative");
result_snomed=260415000;
where (test_result="Not Detected"
or test_result="NOT DETECTED"
or test_result="not detected"
or test_result="Not detected");
result_snomed=280416009;
where (test_result="Indeterminate"
or test_result="INDETERMINATE"
or test_result="indeterminate");
result_snomed=419984006;
where (test_result="Inconclusive"
or test_result="INCONCLUSIVE"
or test_result="inconclusive");
end;

First thing I got this error (after each if statement):

if test_loinc="94558-4" then do; /*Note: this is an antigen test*/
220 result_snomed=10828004;
221 where (test_result="Positive"
222 or test_result="POSITIVE"
223 or test_result="positive");
ERROR: WHERE clause operator requires compatible variables.

Also is there another simpler way to do this rather than having this long code?

Thank you.

Re: WHERE clause operator requires compatible variables Error

PaigeMiller — Wed, 06 Jan 2021 13:42:06 GMT

You are trying to compare a numeric variable named TEST_RESULT to a character string. You can't do that. You have to compare numeric to numeric, or character to character.

it is possible that numeric TEST_RESULT is formatted to appear as character; that would still require you to compare it to a numeric value. Use PROC CONTENTS to find out how TEST_RESULT is formatted.

Re: WHERE clause operator requires compatible variables Error

gamotte — Wed, 06 Jan 2021 13:54:09 GMT

Hello,

The where statement in a data step has a global effect and drops the observations that don't

match the condition before executing the data step intructions.

Thus, it does not make sense to condition these where statements as they will all be taken into account

at the program execution.

Before resolving this type mismatch problem, you might want to reconsider the logic of

your program.

Can you explain on a simplified example what you are trying to achieve ?

Re: WHERE clause operator requires compatible variables Error

Kurt_Bremser — Wed, 06 Jan 2021 14:04:57 GMT

You have two serious mistakes here:

trying to have a conditional WHERE statement. This is not possible, see the documentation.
having multiple WHERE statements. Only the last WHERE statement will be compiled by the data step compiler.

I guess that you should use the conditions in the IF statements; for more detailed help, supply some example data in usable form (DATA step with DATALINES, NOT Excel files or screenshots!), and what you intend to have as a result from that.

Re: WHERE clause operator requires compatible variables Error

mayasak — Wed, 06 Jan 2021 15:01:31 GMT

data;
   input name $ test_loinc $ test_result $;
   datalines;
John 94759-8 Positive
Michelle 94558-4 Not detected
Grace 94759-8 DETECTED
;

So the issue is that we're getting an excel sheet from a hospital that have different variables (in additition to the test_result and test_loinc) and we need to import into to our system. They use only 2 loincs (tests) (used in the example) and the result comes in different formats such as DETECTED, Detected, Positive, positive... etc. What I needed to do is to code all different result formats into the standard snomed (results) codes such as 10828004 for all postive results because our system accepts only codes for loincs and snomeds. BTW I'm not sure if the test_result is a character or numerical but I'm assuming it's a character in the example! Thank you

Re: WHERE clause operator requires compatible variables Error

PaigeMiller — Wed, 06 Jan 2021 15:13:53 GMT

Can you show us a portion of the data set from the imported spreadsheet by following these instructions , and not via any other method. Do not attach the Excel file, most people will not download it.

Re: WHERE clause operator requires compatible variables Error

Kurt_Bremser — Wed, 06 Jan 2021 16:19:45 GMT

Start with a "brute force" approach like this:

select (test_loinc);
  when ("94759-8") do;
    select (upcase(test_result));
      when ("POSITIVE") result_snomed = 260373001;
      when ("DETECTED") result_snomed = 260385009;
      otherwise result_snomed = 10828004;
    end;
  end;
end;

expand this code snippet for the other tests and results.

Re: WHERE clause operator requires compatible variables Error

mayasak — Wed, 06 Jan 2021 18:17:27 GMT

Thank you,

I expanded this to:

select (test_loinc);
when ("94759-8") do;
select (upcase(test_result));
when ("DETECTED") result_snomed = 260373001;
when ("NEGATIVE") result_snomed = 260385009;
when ("POSITIVE") result_snomed = 10828004;
otherwise result_snomed = 260415000;
end;
select (lowcase(test_result));
when ("detected") result_snomed = 260373001;
when ("negative") result_snomed = 260385009;
when ("positive") result_snomed = 10828004;
otherwise result_snomed = 260415000;
end;
select (propcase(test_result));
when ("Detected") result_snomed = 260373001;
when ("Negative") result_snomed = 260385009;
when ("Positive") result_snomed = 10828004;
otherwise result_snomed = 260415000;
end;
end;
when ("94558-4") do;
select (upcase(test_result));
when ("DETECTED") result_snomed = 260373001;
when ("NEGATIVE") result_snomed = 260385009;
when ("POSITIVE") result_snomed = 10828004;
otherwise result_snomed = 260415000;
end;
select (lowcase(test_result));
when ("detected") result_snomed = 260373001;
when ("negative") result_snomed = 260385009;
when ("positive") result_snomed = 10828004;
otherwise result_snomed = 260415000;
end;
select (propcase(test_result));
when ("Detected") result_snomed = 260373001;
when ("Negative") result_snomed = 260385009;
when ("Positive") result_snomed = 10828004;
otherwise result_snomed = 260415000;
end;
end;
end;

Does it look fine? Especially for the use of end;

Thank you,

Razina

Re: WHERE clause operator requires compatible variables Error

gamotte — Wed, 06 Jan 2021 18:40:13 GMT

I don't understand what you are trying to do.

You do exactly the same thing for the different values of test_loinc so why distinguishing several cases ?

Also, for a given value of test_loinc all selects after the first one are redundants since
they test the same condition (although expressed differently).

=> All your code could be replaced by (under the assumption that there are no other values for test_loinc) :
select (upcase(test_result));
when ("DETECTED") result_snomed = 260373001;
when ("NEGATIVE") result_snomed = 260385009;
when ("POSITIVE") result_snomed = 10828004;
otherwise result_snomed = 260415000;
end;

Re: WHERE clause operator requires compatible variables Error

mayasak — Wed, 06 Jan 2021 19:13:51 GMT

There are 2 values for "test_loinc" and more values for "test_result" such as results with all lower case and results with mixedletters (first letter only upper case).

Thank you,

Razina

Re: WHERE clause operator requires compatible variables Error

Kurt_Bremser — Wed, 06 Jan 2021 19:50:29 GMT

What @gamotte said. By testing for UPCASE once you can cover all eventualities, most of your code is redundant.