topic Re: macro with a list of strings from table in SAS Programming

macro with a list of strings from table

mwilk — Wed, 24 Nov 2021 10:30:36 GMT

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

Re: macro with a list of strings from table

PaigeMiller — Wed, 24 Nov 2021 11:28:55 GMT

You already asked this question and marked it solved

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

Re: macro with a list of strings from table

mwilk — Wed, 24 Nov 2021 11:55:05 GMT

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

Re: macro with a list of strings from table

Kurt_Bremser — Wed, 24 Nov 2021 11:57:01 GMT

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;

Re: macro with a list of strings from table

mwilk — Wed, 24 Nov 2021 15:16:31 GMT

thank you very very much!

Re: macro with a list of strings from table

mwilk — Thu, 25 Nov 2021 10:08:30 GMT

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

Re: macro with a list of strings from table

Kurt_Bremser — Thu, 25 Nov 2021 17:04:21 GMT

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.

Re: macro with a list of strings from table

mwilk — Mon, 29 Nov 2021 10:02:01 GMT

Thank you very much!!!