Case when conditions macro

[mwilk]

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?

[PaigeMiller]

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;

[mwilk]

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

[mwilk]

Unfortunately that doesn't work:(

[mwilk]

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.

[s_lassen]

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;

[mwilk]

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, ^,

[s_lassen · Accepted solution]

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

[mwilk]

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

[mwilk]

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.

[mwilk]

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

[Kurt_Bremser]

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;

[mwilk]

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;

[mwilk]

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

[mwilk]

so noone can help on this?