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%'
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;
You already asked this question and marked it solved
https://communities.sas.com/t5/SAS-Programming/Case-when-conditions-macro/m-p/779313
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;
May I only ask what double (!!) on both sides of LIKE are for?
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:
'%'!!"¯ovar."!!'%'
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.
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.