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,
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?
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.
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.
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?
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.
@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?
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.
I just use the macro from that paper, but for beginners I don't mention that.
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=" "
....
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.