topic Re: SAS macro - search for values and assign values from different variables in SAS Programming

SAS macro - search for values and assign values from different variables

mwilk — Tue, 07 Dec 2021 13:25:27 GMT

Hi,

Seeking for a help to build that into working macro.

Case when orig_nm LIKE '%KRAKEN%' then orig_acct_id

when benef_nm LIKE '%KRAKEN%' then benef_acct_id

when scnd_orig_nm LIKE '%KRAKEN%' then scnd_orig_acct_id

when scnd_benef_nm LIKE '%KRAKEN%' then scnd_benef_acct_id

when orig_nm LIKE '%BINANCE%' then orig_acct_id

when benef_nm LIKE '%BINANCE%' then benef_acct_id

when scnd_orig_nm LIKE '%BINANCE%' then scnd_orig_acct_id

when scnd_benef_nm LIKE '%BINANCE%' then scnd_benef_acct_id

Generally the list of crypto platforms in LIKE is huge and dynamic, so I don't want to repeat "when" 300 times and then modify it once I add new entities. I would like a macro that pulls platforms names out of my list search for it in "orig_nm", "benef_nm" etc. and once it finds it, it assigns a value from corresponding acct number fields within the same records.

So I create one variable that holds account numbers for crypto platforms, wherever it finds it. Is there anyone who possibly could help with this?

Re: SAS macro - search for values and assign values from different variables

PaigeMiller — Tue, 07 Dec 2021 13:44:07 GMT

In an earlier thread on this subject, you marked an answer that showed how to do this as the correct answer: https://communities.sas.com/t5/SAS-Programming/macro-with-a-list-of-strings-from-table/m-p/782195#M249359

Re: SAS macro - search for values and assign values from different variables

mwilk — Tue, 07 Dec 2021 13:55:27 GMT

not really. In previous problem, macro that found a crypto platform name within a searched field would create a variable with the value that it was looking for.

For example, before it was:

case when orig_nm LIKE '%KRAKEN%' then 'KRAKEN'

what I'm looking for now is:

case when orig_nm LIKE '%KRAKEN%' then orig_acct_id

This is the difference which I'm unable to resolve using the other macro. Tried but failed to get it working as intended. Hope it clarifies. I was advised to create a new thread which I did.

Regards

Re: SAS macro - search for values and assign values from different variables

PaigeMiller — Tue, 07 Dec 2021 14:59:35 GMT

Show us what you have tried.

Give us a partial list of these crypto platforms

Give us a partial list of the variable names

Re: SAS macro - search for values and assign values from different variables

mwilk — Tue, 07 Dec 2021 15:01:41 GMT

Tried this but didn't succeed. Then tried to change this part "%let field=%scan(&fields,&i);" to "%let field=%scan(&fields,&j);" - didn't succeed either. However, I got help on my old thread. So I got a solution already 🙂

%macro new_loop(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 field=%scan(&fields,&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(&field,%str(%')))
%put _local_;
%end;
%mend;

Re: SAS macro - search for values and assign values from different variables

PaigeMiller — Tue, 07 Dec 2021 17:02:33 GMT

That's good news you got a solution.

As a tip for the future, saying “didn't succeed” with no other explanation never works.

We need to see the LOG, if there are errors in the log; or we need to see the incorrect output. And an explanation of “didn't succeed” is almost always helpful.

Re: SAS macro - search for values and assign values from different variables

mwilk — Wed, 08 Dec 2021 10:23:42 GMT

Thank you!