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!
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
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
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).
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.