check for macro variable in where clause

[vncntjgw]

I have the following macro and sometime the variable in the where clause will exist and sometimes it won't.

%macro mem_det(tbl_, lice, sptc, type);
 proc sql;

  create table &tbl_ as
   select distinct
    ma_id
    ,i_id
   from data.Northwinds
   where
    License = "&lice"
    and UPCASE(TRIM(topic_name)) in ("&sptc")
    if %symexist(type) %then and Type = ("&type");
   run;
  quit;
%mend mem_det

%mem_det(tbl_= TR_TEL_LIC, lice='LICENSED', sptc="contact", type=) /** in this instance the type isn't available **/
%mem_det(tbl_= TR_TEL_NONLIC, lice='NON-LICENSED', sptc="contact", type="FTE")

Thanks,

[Astounding]

A couple of things jump out.  

You have to use %if, not if.  But %symexist is the wrong tool for the job.  TYPE is a parameter so it always exists, although it can have a null value.  Better:

%if %length(&type) %then and type="&type"; ;

Also note, there can never be any observations selected when you enter SPTC  in lower case letters.  It can never match a value returned by the UPCASE function.

There may be other issues, but try this much and see if it solves what you need done.

The first semicolon ends the %IF %THEN statement.  The second ends the CREATE statement.

[vncntjgw]

Sorry, I'll correct that %If statement.

[PaigeMiller]

Since TYPE is an argument to the macro, it always exists, but it may be empty.

Thus, you may want to check to see if &type is empty or not.

%if &type^= %then and type = "&type";

I think you have other errors in your code as well. 

[vncntjgw]

Thanks, I'll give it a try.

[Tom · Accepted solution]

What is it that doesn't exist? The value of the macro variable? Or that variable in the data that the condition is testing?

Note that using the MPRINT option can help you see what code your macro is generating.

No need to add quotes around the values in the macro if the call already has the quotes. In fact trying to do both will cause trouble.

No need to TRIM() values before comparing them. SAS ignores trailing spaces when comparing values.

Make sure to upcase the comparison string if you are upcasing the values in the data.

%macro mem_det(tbl_, lice, sptc, type);
proc sql;
  create table &tbl_ as
   select distinct
     ma_id
    ,i_id
   from data.Northwinds
   where License = &lice
%if %length(&type) %then
     and Type = &type
;
     and upcase(topic_name) in (%upcase(&sptc))
  ;
quit;
%mend mem_det

/** in this instance the type isn't available **/
%mem_det(tbl_= TR_TEL_LIC, lice='LICENSED', sptc="contact", type=) 
%mem_det(tbl_= TR_TEL_NONLIC, lice='NON-LICENSED', sptc="contact", type="FTE")

Do you need to test if TYPE exists in the input data data.Northwinds?

[vncntjgw]

We're checking to see if the value of the TYPE is passed in the macro. If it's not passed then we won't need the "and Type=&Type" filter in the where statement.

[Tom]

The easiest test for whether user provided a value is to use the %LENGTH() macro function to test if the parameter value is not empty.

[PaigeMiller]

---

@Tom wrote:

The easiest test for whether user provided a value is to use the %LENGTH() macro function to test if the parameter value is not empty.

---

How is that easier?

[Tom]

Because you don't have worry that the value will be look like code to the macro processor.

To see a good inventory of the types of problems see this classic paper by Chung and King.

https://support.sas.com/resources/papers/proceedings09/022-2009.pdf

Although in their analysis they also want to consider a macro variable that is non-empty but only contains space characters as being "blank".  You could add that level of testing if you want but it is normally not worth it as a user has work pretty hard to pass actual blanks into a parameter value in a macro call.

[PaigeMiller]

I just use the macro from that paper, but for beginners I don't mention that.

[Tom]

Looking at that Chung and King paper again the only blemish they found on using %LENGTH() was that they claimed it failed to detect when the value only contains blanks.  But that is not normally a test that I need or want to make. 

If the user wants to go to the effort to force the parameter TYPE to be one or more blanks instead of empty then the macro can just use those blanks as the value in the code it generates.

%mymacro(type=%str(   ));
....
MPRINT(MYMACRO):  and type="   "
....

[vncntjgw]

This was way awesome. It's working perfectly!