Case when conditions macro

[Tom]

Do you want to start a new thread for the new question?

In general if the code generation is too complex you are better off using a DATA step to generate the code instead of macro code.  You will be able to debug the code much easier.

[mwilk]

Actually macro works well for the of the problem I had. This one is very similar but instead of returning the value when condition met I need it to return value from different variable. Macro seems to be the answer for such "case when" with multiple conditions. Can't think of the data step to resolve this. Number of "whens" is dynamic and comes from the external file. So, yes, macro seems to be the answer. The problem is I'm struggling to get it to work! 🙂

[Tom]

Spend some time to explain clearly what you want.  I cannot find any clear explanation of the rule that describes what you want to happen.  The best I can do it deconstruct what the accepted solution is doing.

It looks like you want to pass in a list of variable names (the macro uses a parameter name of FIELDS) and list of substrings to search for (the macro uses a parameter name of TEXTS).  It then loops over all combinations and generates a

WHEN (xxx) THEN yyy

part of a CASE statement.

Looks like your new requirement needs a much simpler XXX part and a different value for the YYY part.

Instead a variable name (or field name) you want to pass in only the prefix that will be used to generate the two variable names.

%macro case_cond
(texts                                             
,prefix=orig benef send
);
%local i j var1 var2 like ;                                         
%let texts=%upcase(&texts);                                       
%do i=1 %to %sysfunc(countw(&texts));                             
  %let like=%unquote(%str(%'%%)%qscan(&texts,&i)%str(%%%'));                                     
  %do j=1 %to %sysfunc(countw(&prefix));
    %let var1=%scan(&prefix,&j)_nm;
    %let var2=%scan(&prefix,&j)_acct_id;
when upcase(&var1) like &like then &var2 
  %end;                                                         
%end;                                                           
%mend;

Let's test it by just use %PUT to print the code it generates.

2068  %put %case_cond(kraCken,prefix=orig benef send);
when upcase(orig_nm) like '%KRACKEN%' then orig_acct_id 
when upcase(benef_nm) like '%KRACKEN%' then benef_acct_id 
when upcase(send_nm) like '%KRACKEN%' then send_acct_id

[mwilk]

Sir, you are a star! Thank you for this. It works perfectly! Moreover I will be able to use it now for other needs.

What is more important is I believe I finally got an idea about how to create such things so I learned from that too. Hope not to spam the forum with similar questions anymore. Once again, thank you very very much!!!

[mwilk]

My one last question would be:

%macro crypto_type_loop
(texts
,prefix=ORIG BENEF SCND_ORIG SCND_BENEF SEND_INSTN
);
%local i j var1 var2 like ;
%let texts=%upcase(&texts);
%do i=1 %to %sysfunc(countw(&texts));
%let like=%unquote(%str(%'%%)%qscan(&texts,&i)%str(%%%'));
%do j=1 %to %sysfunc(countw(&prefix));
%let var1=%scan(&prefix,&j)_nm;
%let var2=%scan(&prefix,&j)_acct_id_type_cd;
%let var3=%scan(&prefix,&j)_id_type_cd;
%if &prefix = "SEND_INSTN" %then %do; when upcase(&var1) like &like then &var3 %end;
when upcase(&var1) like &like then &var2;
%end;
%end;
%mend;

I tried to create a var3 so that for send_nm prefix, variable would be slightly different. 

Would you know what I have done wrong here?

In other words for all variables the assigning variable would be "_acct_id_type_cd" but for SEND_NM I need "_id_type_cd". 

I thought this would be ease but doesn't work....

[mwilk]

Looks like I got it working. Does it look fine?:)

%macro crypto_type_loop
(texts
,prefix=ORIG BENEF SCND_ORIG SCND_BENEF SEND_INSTN
);
%local i j var1 var2 like ;
%let texts=%upcase(&texts);
%do i=1 %to %sysfunc(countw(&texts));
%let like=%unquote(%str(%'%%)%qscan(&texts,&i)%str(%%%'));
%do j=1 %to %sysfunc(countw(&prefix));
%let var1=%scan(&prefix,&j)_nm;
%let var2=%scan(&prefix,&j)_acct_id_type_cd;
%let var3=%scan(&prefix,&j)_id_type_cd;
%if %scan(&prefix,&j) = SEND_INSTN %then %do; when upcase(&var1) like &like then &var3 %end;
%if %scan(&prefix,&j) ne SEND_INSTN %then %do; when upcase(&var1) like &like then &var2 %end;
%end;
%end;
%mend;

[Tom]

It is probably easier/clearer to use IF/THEN/ELSE instead of two completely independent IF/THEN.

Also it is probably clearer to use the macro code to set the variable name conditionally and then generate the SAS code unconditionally.

%macro case_cond
(texts                                             
,prefix=send_instn
);
%local i j var1 var2 like ;                                         
%let texts=%upcase(&texts);                                       
%do i=1 %to %sysfunc(countw(&texts));                             
  %let like=%unquote(%str(%'%%)%qscan(&texts,&i)%str(%%%'));                                     
  %do j=1 %to %sysfunc(countw(&prefix));
    %let var2=%scan(&prefix,&j);
    %let var1=&var2._nm;
    %if %upcase(&var2) ne SEND_INSTN %then %let var2=&var2._acct;
    %let var2=&var2._id_type_cd;
when upcase(&var1) like &like then &var2 
  %end;                                                         
%end;                                                           
%mend;

Results:

2125  %put %case_cond(ABC,prefix=orig send_instn);
when upcase(orig_nm) like '%ABC%' then orig_acct_id_type_cd 
when upcase(send_instn_nm) like '%ABC%' then send_instn_id_type_cd

[mwilk]

Thank you very much Tom!!!! Very helpful!

[Kurt_Bremser]

Do you want precedence as defined by the order of the comparisons?

What if orig_nm = "coinbase" and benef_nm = "bitfinex" and scnd_orig_nm = "kraken"?

[mwilk]

I'm not worried about that. That should not happen. Thanks

[Kurt_Bremser]

---

@mwilk wrote:

I'm not worried about that. That should not happen. Thanks

---

"Should not" is not "can't". Code a safeguard against that.