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?
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%'))
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;
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;
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%'))
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.
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;
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....
so noone can help on this?
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.