macro with a list of strings from table

[mwilk]

Hi guys,

I'm trying (for a few days) to make a code below a macro loop. I would like to one of the where conditions to be a macro where strings like "coinbase" etc are iterating along with fields I want them to be searched within. I have like 70 of those strings and don't want to repeat 100 times. Can anyone help me to create a macro that actually works? I have created one but only when I put one name in it it works. If I put more, then it downloads everything like there was no condition....I have a list of crypto platforms in excel csv sheet and want my macro to take it from there and search for each word within 4 variables. Anyone could help with it? Spend few days but no luck:(

where
( UPPER(WT.ORIG_NM) LIKE '%COINBASE%'
OR UPPER(WT.ORIG_NM) LIKE '%BITFINEX%'
OR UPPER(WT.ORIG_NM) LIKE '%KRAKEN%'
OR UPPER(WT.ORIG_NM) LIKE '%BINANCE%'
OR UPPER(WT.SCND_ORIG_NM) LIKE '%COINBASE%'
OR UPPER(WT.SCND_ORIG_NM) LIKE '%BITFINEX%'
OR UPPER(WT.SCND_ORIG_NM) LIKE '%KRAKEN%'
OR UPPER(WT.SCND_ORIG_NM) LIKE '%BINANCE%'
OR UPPER(WT.BENEF_NM) LIKE '%COINBASE%'
OR UPPER(WT.BENEF_NM) LIKE '%BITFINEX%'
OR UPPER(WT.BENEF_NM) LIKE '%KRAKEN%'
OR UPPER(WT.BENEF_NM) LIKE '%BINANCE%'

[PaigeMiller]

You already asked this question and marked it solved

https://communities.sas.com/t5/SAS-Programming/Case-when-conditions-macro/m-p/779313

[mwilk]

No it was similar but different.... It was for case when. I tried to use and modify that macro for a week to make it work in this case but failed. Hence my ask for a help from people who knows how to do it....

[Kurt_Bremser · Accepted solution]

data wt;
input orig_nm :$20. scnd_orig_nm :$20.;
datalines;
xxcoinbasezz yyy
xxx yyy
xxx yyykrakenzz
;

%macro loop_over(variables,values);
%local i j;
%do i = 1 %to %sysfunc(countw(&variables.));
  %let var=%scan(&variables,&i.);
  %do j = 1 %to %sysfunc(countw(&values.));
    %if &j. ne 1 or &i. ne 1 %then %do; or %end;
    upcase(%scan(&variables,&i.)) like "%"!!"%scan(&values,&j.)"!!"%"
  %end;
%end;
%mend;

proc sql;
select * from wt
where (
%loop_over(orig_nm scnd_orig_nm,COINBASE KRAKEN)
);
quit;

[mwilk]

thank you very very much!

[mwilk]

May I only ask what double (!!) on both sides of LIKE are for?

[Kurt_Bremser]

Two exclamation marks are the character concatenation operator (alternatively, you can use two pipe

||

characters).

Splitting the percent signs away from the rest of the strings tricks the macro processor into not recognizing them as macro triggers. To mask them even further, you could use single quotes around the percent signs only:

'%'!!"&macrovar."!!'%'

The macro processor will only resolve the macro variable, and SQL will concatenate the strings to the %VALUE% you want. And SQL will first concatenate and then recognize the special LIKE characters.

[mwilk]

Thank you very much!!!