topic Re: Case when conditions macro in SAS Programming

Case when conditions macro

mwilk — Tue, 09 Nov 2021 12:28:32 GMT

Hi guys,

I have a problem with a macro (loop) that would create as many conditions as values I have in my column...

I have a code like this:

case when UPPER(ORIG_NM) LIKE '%COINBASE%'
OR UPPER(BENEF_NM) LIKE '%COINBASE%'
OR UPPER(SCND_ORIG_NM) LIKE '%COINBASE%'
OR UPPER(SCND_BENEF_NM) LIKE'%COINBASE%'
OR UPPER(SEND_INSTN_NM) LIKE '%COINBASE%'
/* OR UPPER(ORIG_TO_BENEF_INSTR_TX) LIKE '%COINBASE%' */
OR UPPER(RCV_INSTN_NM) LIKE'%COINBASE%' then 'COINBASE'

when UPPER(ORIG_NM) LIKE '%BITFINEX%'
OR UPPER(BENEF_NM) LIKE '%BITFINEX%'
OR UPPER(SCND_ORIG_NM) LIKE '%BITFINEX%'
OR UPPER(SCND_BENEF_NM) LIKE'%BITFINEX%'
OR UPPER(SEND_INSTN_NM) LIKE '%BITFINEX%'
/* OR UPPER(ORIG_TO_BENEF_INSTR_TX) LIKE '%BITFINEX%' */
OR UPPER(RCV_INSTN_NM) LIKE'%BITFINEX%' then 'BITFINEX'

when UPPER(ORIG_NM) LIKE '%KRAKEN%'
OR UPPER(BENEF_NM) LIKE '%KRAKEN%'
OR UPPER(SCND_ORIG_NM) LIKE '%KRAKEN%'
OR UPPER(SCND_BENEF_NM) LIKE'%KRAKEN%'
OR UPPER(SEND_INSTN_NM) LIKE '%KRAKEN%'
/* OR UPPER(ORIG_TO_BENEF_INSTR_TX) LIKE '%KRAKEN%' */
OR UPPER(RCV_INSTN_NM) LIKE'%KRAKEN%' then 'KRAKEN'

now, that 3 names (kraken, coinbase, bitfinex) are only 3 examples. I have a hundred of them and I would like not to repeat that section manually 100 times but do the loop that would add this section to my code as many times as many I have record in the table that the macro would pull the values from. Any ideas?

Re: Case when conditions macro

PaigeMiller — Tue, 09 Nov 2021 12:38:15 GMT

UNTESTED CODE

%macro conditions(names=);
    %do i=1 %to %sysfunc(countw(&names));
        %let thisname=%scan(&names,i,%str( ));
        case when upper(orig_nm) like "%nrstr(%%)&thisname%nrstr(%%)"
        or upper(benef_nm) like "%nrstr(%%)&thisname%nrstr(%%)"
        or  /* I'm lazy, you type the rest */
        then "&thisname"
     %end;
%mend;

proc sql;
      /* Some appropriate SQL code */
      %conditions(names=COINBASE BITFINEX KRAKEN BUFFALO SYRACUSE ROCHESTER UTICA ALBANY)
      /* more appropriate SQL code */
quit;

Re: Case when conditions macro

s_lassen — Tue, 09 Nov 2021 13:46:20 GMT

Here is a macro that can iterate both the fields and the texts:

%macro case_cond(texts,                                             
  fields=ORIG_NM BENEF_NM SCND_ORIG_NM SCND_BENEF_NM SEND_INSTN_NM);
  %local i j like_str text;                                         
  %let texts=%upcase(&texts);                                       
  %do i=1 %to %sysfunc(countw(&texts));                             
    %let text=%scan(&texts,&i);                                     
     %let like_str=%unquote(%nrstr(%'%%)&text%nrstr(%%%'));         
       when upper(%scan(&fields,1)) like &like_str                 
    %do j=2 %to %sysfunc(countw(&fields));                          
      or upper(%scan(&fields,&j)) like &like_str                    
      %end;                                                         
    then %str(%'&text%')                                            
    %end;                                                           
%mend;

So you would generate the code you supplied like this:

%case_cond(COINBASE BITFINEX KRAKEN)

And if you also wanted the lines you commented out, you can do it like this:

%case_cond(COINBASE BITFINEX KRAKEN,
           fields=ORIG_NM BENEF_NM SCND_ORIG_NM SCND_BENEF_NM ORIG_TO_BENEF_INSTR_TX SEND_INSTN_NM)

The macro can be made a bit simpler if you use the CONTAINS or ? condition instead of LIKE (? "TEST" is the same as LIKE '%TEST%'):

%macro case_cond(texts,                                             
  fields=ORIG_NM BENEF_NM SCND_ORIG_NM SCND_BENEF_NM SEND_INSTN_NM);
  %local i j text;                                                  
  %let texts=%upcase(&texts);                                       
  %do i=1 %to %sysfunc(countw(&texts));                             
    %let text="%scan(&texts,&i)";                                   
     when upper(%scan(&fields,1)) ? &text                       
    %do j=2 %to %sysfunc(countw(&fields));                          
       or upper(%scan(&fields,&j)) ? &text                          
      %end;                                                         
    then &text                                                      
    %end;                                                           
%mend;

Edit note: I took the CASE keyword out of the macro (I had misplaced it, any way), so now the macro should be used like e.g.:

proc sql;
  select name,
     case %case_cond(a b c)
     else 'NOTHING'
    end as test
  from x;
quit;

Re: Case when conditions macro

Kurt_Bremser — Tue, 09 Nov 2021 13:17:58 GMT

Do you want precedence as defined by the order of the comparisons?

What if orig_nm = "coinbase" and benef_nm = "bitfinex" and scnd_orig_nm = "kraken"?

Re: Case when conditions macro

mwilk — Tue, 09 Nov 2021 16:20:35 GMT

I'm not worried about that. That should not happen. Thanks

Re: Case when conditions macro

mwilk — Tue, 09 Nov 2021 16:21:04 GMT

Thank you! I will try to make a use of this.

Re: Case when conditions macro

mwilk — Tue, 09 Nov 2021 16:33:13 GMT

Unfortunately that doesn't work:(

Re: Case when conditions macro

mwilk — Tue, 09 Nov 2021 16:34:41 GMT

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric
operand is required. The condition was: i
ERROR: Argument 2 to macro function %SCAN is not a number.
ERROR: The macro CONDITIONS will stop executing.

Re: Case when conditions macro

mwilk — Tue, 09 Nov 2021 16:42:49 GMT

Unfrtunately I can't get it working but will spend some time on it tomorrow. This is not an easy task I know 🙂

311 proc sql;
312 select *,
313 case %case_cond(COINBASE BITFINEX KRAKEN)
NOTE 137-205: Line generated by the invoked macro "CASE_COND".
16 then '&text'
-
22
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string,
a numeric constant, a datetime constant, a missing value, (, +, -, BTRIM,
CALCULATED, CASE, EXISTS, INPUT, NOT, PUT, SELECT, SUBSTRING, TRANSLATE, USER, ^,

Re: Case when conditions macro

Kurt_Bremser — Tue, 09 Nov 2021 17:35:08 GMT

@mwilk wrote:

I'm not worried about that. That should not happen. Thanks

"Should not" is not "can't". Code a safeguard against that.

Re: Case when conditions macro

s_lassen — Wed, 10 Nov 2021 08:20:59 GMT

You're right, there is some macro quoting problems in the first macro (I only tested the second version).

Try this instead:

%macro case_cond(texts,                                             
  fields=ORIG_NM BENEF_NM SCND_ORIG_NM SCND_BENEF_NM SEND_INSTN_NM);
  %local i j like_str text;                                         
  %let texts=%upcase(&texts);                                       
  %do i=1 %to %sysfunc(countw(&texts));                             
    %let text=%scan(&texts,&i);                                     
     %let like_str=%unquote(%nrstr(%'%%)&text%nrstr(%%%'));         
       when upper(%scan(&fields,1)) like &like_str                  
    %do j=2 %to %sysfunc(countw(&fields));                          
      or upper(%scan(&fields,&j)) like &like_str                    
      %end;                                                         
    then %sysfunc(quote(&text,%str(%')))                            
    %put _local_;                                                   
    %end;                                                           
%mend;

(I used the %sysfunc(quote()) instead of %str(%'&text%'))

Re: Case when conditions macro

mwilk — Tue, 16 Nov 2021 09:54:02 GMT

works like a charm! Thanks! Now, looking to make it work inside a passthrough oracle query:)

Re: Case when conditions macro

mwilk — Tue, 16 Nov 2021 10:43:29 GMT

Since you have been extremely helpful I have one more question. I try to use very similar macro for simple WHERE clause and I got:

%macro filter_cond(texts,
fields=ORIG_NM BENEF_NM SCND_ORIG_NM SCND_BENEF_NM);
%local i j like_str text;
%let texts=%upcase(&texts);
%do i=1 %to %sysfunc(countw(&texts));
%let text=%scan(&texts,&i);
%let like_str=%unquote(%nrstr(%'%%)&text%nrstr(%%%'));
upper(%scan(&fields,1)) like &like_str or
%do j=2 %to %sysfunc(countw(&fields));
upper(%scan(&fields,&j)) like &like_str or
%end;
%put _local_;
%end;
%mend;

The problem is the last iteration still puts "or" after last string search and query doesn't work because of that.

Would you know how to remove last "or"? Tried something like -1 to each iteration but that applies to more than doesn't work since we have a loop in the loop and we need to remove "or" from the very last line generated.

Re: Case when conditions macro

mwilk — Tue, 16 Nov 2021 10:44:27 GMT

proc sql noprint;
create table test as
select *
from db
where %filter_cond(&platforms)
;
quit;

Re: Case when conditions macro

Kurt_Bremser — Tue, 16 Nov 2021 10:53:16 GMT

Make the "or" conditional:

%do j=2 %to %sysfunc(countw(&fields));
  upper(%scan(&fields,&j)) like &like_str 
  %if &j. ne %sysfunc(countw(&fields) then or;
%end;

Re: Case when conditions macro

mwilk — Tue, 16 Nov 2021 11:24:20 GMT

This will remove "or" for each main loop iteration which won't work.

I have came up with something that works but probably is not well optimized:

%macro filter_cond(texts,
fields=ORIG_NM BENEF_NM SCND_ORIG_NM SCND_BENEF_NM);
%local i j like_str text;
%let texts=%upcase(&texts);
%do i=1 %to 1;
%let text=%scan(&texts,&i);
%let like_str=%unquote(%nrstr(%'%%)&text%nrstr(%%%'));
upper(%scan(&fields,1)) like &like_str
%do j=2 %to %sysfunc(countw(&fields));
or upper(%scan(&fields,&j)) like &like_str
%end;
%put _local_;
%end;
%do i=2 %to %sysfunc(countw(&texts));
%let text=%scan(&texts,&i);
%let like_str=%unquote(%nrstr(%'%%)&text%nrstr(%%%'));
or upper(%scan(&fields,1)) like &like_str
%do j=2 %to %sysfunc(countw(&fields));
or upper(%scan(&fields,&j)) like &like_str
%end;
%put _local_;
%end;
%mend;

Re: Case when conditions macro

mwilk — Fri, 03 Dec 2021 14:21:33 GMT

Who possibly can help me to modify this macro, so that once it finds the required text in particular field, instead of putting this text as a value, it will put a value from different variable from that record. Something like that in macro:

Case when benef_nm LIKE '%KRAKEN%' then benef_acct_id

when orig_nm LIKE '%KRAKEN%' then orig_acct_id

when send_nm LIKE '%BINANCE%' then send_acct_id

Before, it was like:

Case when benef_nm LIKE '%KRAKEN%' then 'KRAKEN'

when orig_nm LIKE '%KRAKEN%' then 'KRAKEN'

when send_nm LIKE '%BINANCE%' then 'BINANCE'

anyone knows how to modify this? I'm struggling here....

Re: Case when conditions macro

mwilk — Mon, 06 Dec 2021 12:30:45 GMT

so noone can help on this?

Re: Case when conditions macro

Tom — Mon, 06 Dec 2021 19:01:57 GMT

Do you want to start a new thread for the new question?

In general if the code generation is too complex you are better off using a DATA step to generate the code instead of macro code. You will be able to debug the code much easier.

Re: Case when conditions macro

mwilk — Tue, 07 Dec 2021 09:08:14 GMT

Actually macro works well for the of the problem I had. This one is very similar but instead of returning the value when condition met I need it to return value from different variable. Macro seems to be the answer for such "case when" with multiple conditions. Can't think of the data step to resolve this. Number of "whens" is dynamic and comes from the external file. So, yes, macro seems to be the answer. The problem is I'm struggling to get it to work! 🙂