topic Re: How can I replace a long IF/THEN or CASE statement with many OR's?? in SAS Programming

How can I replace a long IF/THEN or CASE statement with many OR's??

GBL__ — Tue, 28 Jan 2020 13:06:24 GMT

Good Morning!

I am struggling to find a solution to create a more concise definition for a created variable. As of now, am I using the below CASE statement:

CASE
    WHEN administrative_status IN ("BK", "13") OR remark_code1 = "BK" OR remark_code2 = "BK" OR remark_code3 = "BK" OR remark_code4 = "BK OR 
    remark_code5 = "BK" OR remark_code6 = "BK" OR remark_code7 = "BK" OR remark_code8 = "BK" OR remark_code9 = "BK" OR remark_code10 = "BK" THEN "BK"
    ELSE ""
END AS LEGAL_INDICATOR_c ,

INDEXC will not work because there are multiple codes with 'B' or 'K' characters, and INDEXW can only search one variable at a time. I am sure there is an easy solution that I am missing (I mean, there has to be, right??).

I know that this is INCORRECT usage but something like:

FIND("BK", remark_code1, remark_code2, remark_code3, remark_code4, remark_code5, remark_code6, remark_code7, remark_code8, remark_code9, remark_code10)

Thanks for your help! Any feedback is appreciated!

Re: How can I replace a long IF/THEN or CASE statement with many OR's??

s_lassen — Tue, 28 Jan 2020 13:22:38 GMT

Maybe something like this will work:

case
  when indexw(catx(' ',administrative_status,of remark_code1-remark_code10),'BK') or administrative_status='13' then 'BK' 
  else '' 
end as legal_indicator_c

Re: How can I replace a long IF/THEN or CASE statement with many OR's??

FreelanceReinh — Tue, 28 Jan 2020 14:01:49 GMT

Hello @GBL__,

Variable lists or arrays, which could make this definition more concise, are not available in PROC SQL. Here are three options:

Option 1: Use a DATA step, e.g.:

data want;
set have;
array remark_code[10];
if administrative_status in ('BK', '13') or 'BK' in remark_code
  then legal_indicator_c='BK';
  else legal_indicator_c=' ';
run;

Option 2: Create the repetitive code with a DATA or PROC SQL step. In your example (with only 10 repetitions) the resulting code will not be (much) shorter, though.

Option 3: Consider transposing your data to obtain a "long" data structure where the 10 values of remark_code1-remark_code10 would be stored in 10 observations of a single variable remark_code. This structure would be more suitable for PROC SQL as you could apply summary functions together with a GROUP BY clause (grouping those 10 observations).

Re: How can I replace a long IF/THEN or CASE statement with many OR's??

Tom — Tue, 28 Jan 2020 14:14:38 GMT

For that specific test the WHICHC() function could be useful.

if whichc('BK',of administrative_status remark_code1-remark_code10)
or administrative_status = "13" then LEGAL_INDICATOR_C = 'BK';
else LEGAL_INDICATOR_C = '  ';

Note that it is a little harder if you are required to use SQL since SAS does not support variable lists in SQL code. You will have to list all 10 of the remark_code variables.

CASE
    WHEN administrative_status = "13" then 'BK'
    WHEN whichc('BK',administrative_status,remark_code1,remark_code2,remark_code3
               ,remark_code4,remark_code5,remark_code6,remark_code7,remark_code8
               ,remark_code9,remark_code10) then 'BK'
    ELSE '  '
END AS LEGAL_INDICATOR_C

Re: How can I replace a long IF/THEN or CASE statement with many OR's??

GBL__ — Wed, 29 Jan 2020 14:52:11 GMT

I thought it might be something via WHICHC or CHOOSEC, but wasn't sure how to implement either. Thank you for your help!!