topic Invalid Option name OR in SAS Programming

Invalid Option name OR

David_Billa — Wed, 22 Sep 2021 11:03:55 GMT

In the following code, I'd like to know about the error which I made. is it duo to missing or incorrect parenthesis?

I'm getting the Invalid Option name OR error when I execute this code

proc Summary data=&_INPUT1(where=(KEY IN ("&clm_paid.","&alae_paid.") AND 
    (input(CALENDAR_MONTH,anydtdte7.) lt REPORTING_DT))
    OR 
    (KEY IN ("&clmcase.","&ala_case.") AND 
    intnx('month' ,input(CALENDAR_MONTH,anydtdte7.),0,'e') eq intnx('qtr',&REPORTINGDATE.,-1,'e')))

    class BRANCH SEGMENT BUSINESS;
    VAR CONTRACT_PERIOD_OF_ACCIDENT;
    output OUT=list;
run;

Re: Invalid Option name OR

ErikLund_Jensen — Wed, 22 Sep 2021 11:19:44 GMT

Hi @David_Billa

The syntax is (where=( expression ));

So make sure you have balanced parantheses in the expresson, so the number of right parantheses is equal to the number of right parantheses, so you don't by accident have an extra paranthesis that ends the where=( .

Re: Invalid Option name OR

PaigeMiller — Wed, 22 Sep 2021 12:12:20 GMT

In most (all?) SAS editors, you can check for imbalanced parentheses. For example, in Base SAS Enhanced Editor, place your cursor to the left of the last parenthesis and then press Ctrl-[ and the cursor should move to the corresponding parenthesis. When I do this on the text

(where=(KEY IN ("&clm_paid.","&alae_paid.") AND 
    (input(CALENDAR_MONTH,anydtdte7.) lt REPORTING_DT))
    OR 
    (KEY IN ("&clmcase.","&ala_case.") AND 
    intnx('month' ,input(CALENDAR_MONTH,anydtdte7.),0,'e') eq intnx('qtr',&REPORTINGDATE.,-1,'e')))

I find no imbalance from start to end. That doesn't mean there aren't imbalances within, so you can then (for example) place the cursor to the left of the 2nd right parenthesis after lt reporting_dt, and press Ctrl-[ and the cursor will move to the corresponding left parenthesis. Now you have identified the problem, the WHERE= ( ) has ended after lt reporting_dt)). So you can't use OR after that, and you will need to modify the parentheses to get this to work.

From now on, when you get an error in the log, please please please please please please please please please show us the entire log for this step, preserving the formatting by copying the log as text clicking on the </> icon and pasting the log into the window that appears.

Re: Invalid Option name OR

Astounding — Wed, 22 Sep 2021 20:13:22 GMT

Both by inspecting the code and from the error message, you have a balanced set of parentheses here:

where=(KEY IN ("&clm_paid.","&alae_paid.") AND 
    (input(CALENDAR_MONTH,anydtdte7.) lt REPORTING_DT))

This means the WHERE clause ends here, and OR is not part of the WHERE clause. Thus the message about the invalid OR option.

While you know the logic that you want the best, it seems the second ")" after REPORTING_DT should be moved to the end of the WHERE clause, giving four closed parentheses instead of three.

Re: Invalid Option name OR

Kurt_Bremser — Wed, 22 Sep 2021 20:32:02 GMT

As long as you write badly formatted "spaghetti" code with no consistent indentation, you deprive yourself of a visual identification of functional blocks.

proc Summary
  data=&_INPUT1 (
    where=(
      KEY IN ("&clm_paid.","&alae_paid.") AND (
        input(CALENDAR_MONTH,anydtdte7.) lt REPORTING_DT
      )
    )
    OR (
      KEY IN ("&clmcase.","&ala_case.") AND 
      intnx('month' ,input(CALENDAR_MONTH,anydtdte7.),0,'e') eq intnx('qtr',&REPORTINGDATE.,-1,'e')
    )
  )

You can now easily see that the keyword OR appears on the same hierarchical level as the WHERE= dataset option, and is therefore interpreted as another (invalid) dataset option.

You also miss the semicolon that must terminate the PROC SUMMARY statement.

Re: Invalid Option name OR

ChrisNZ — Thu, 23 Sep 2021 00:21:59 GMT

100% with @Kurt_Bremser here.

Code should be pretty. You spend seconds to align and format it, and save minutes or hours to understand, debug, and maintain it.