topic Re: Proc SQL- Where clause question in SAS Programming

Proc SQL- Where clause question

wlierman — Wed, 15 Apr 2020 17:37:46 GMT

I have nine diagnosis code attributes (columns) labeled Diag1, Diag2, ..... , Diag9

I have several icd-10 dx_codes that I need to check across 3 years of Medicaid recipient claims

Here is the c ode starting after the FROM clause

WHERE Month Between '201701' And '201912'

And ClaimType Not In ('D')

And Diag1 - Diag9 In ('B20', 'B9735', ........., '09873')

ORDER By Recipno, Month;

quit;

The problem occurs at Diag1- Diag9 with the error reading:

Expression using negation (-) requires numeric types

I have tried several variants of the Diag1-DIag9 construct but none have solved the issue.

I appreciate your help again.

Walt Lierman

Re: Proc SQL- Where clause question

PGStats — Wed, 15 Apr 2020 18:01:10 GMT

You can't use variable lists in proc SQL. Try the condition

And
whichc(Diag1, 'B20', 'B9735', ........., '09873') +
whichc(Diag2, 'B20', 'B9735', ........., '09873') +
whichc(Diag3, 'B20', 'B9735', ........., '09873') +
whichc(Diag4, 'B20', 'B9735', ........., '09873') +
whichc(Diag5, 'B20', 'B9735', ........., '09873') +
whichc(Diag6, 'B20', 'B9735', ........., '09873') +
whichc(Diag7, 'B20', 'B9735', ........., '09873') +
whichc(Diag8, 'B20', 'B9735', ........., '09873') +
whichc(Diag9, 'B20', 'B9735', ........., '09873') > 0

which is probably more efficient than a condition involving the OR operator.Diag1 in (...) OR Diag2 in (...) ...

Re: Proc SQL- Where clause question

Tom — Wed, 15 Apr 2020 18:02:06 GMT

Variable lists do not work in SQL code, only in regular SAS code, that is why it thought you wanted subtraction.

But even if they did work your syntax will not work. SAS does not have a syntax for test M variables for N values.

You need to list all of the test separately.

WHERE Month between '201701' and '201912'
    and ClaimType Not In ('D')
    and (Diag1 in ('B20', 'B9735', ........., '09873')
      or Diag2 in ('B20', 'B9735', ........., '09873')
   ...
      or Diag9 in ('B20', 'B9735', ........., '09873')
        )

Re: Proc SQL- Where clause question

wlierman — Wed, 15 Apr 2020 18:38:39 GMT

Thank you for your help on this.
Appreciate it very much.

Re: Proc SQL- Where clause question

wlierman — Tue, 21 Apr 2020 13:47:10 GMT

Thank you Tom.

I appreciate your time and effort and correction.

Walt L