Proc SQL- Where clause question

[wlierman]

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

[PGStats]

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 (...) ...

[Tom · Accepted solution]

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')
        )

[wlierman]

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

[wlierman]

Thank you Tom.

I appreciate your time and effort and correction.

Walt L