Macro variable - %Let Where

[DougHold]

I'm trying to learn basic macros, playing around using the sashelp.cars dataset. The following prints the cars data by specific type and origin:

%LET type_name = 'Sports';
%LET origin_location = 'Europe';
%MACRO Printcars;
PROC PRINT DATA = sashelp.cars;
WHERE type = &type_name and origin = &origin_location;
RUN;
%MEND Printcars;
%Printcars

I would also like to be able to specify 'anything'. For example, I want the type to be any type, but origin is Europe (or type is sports and origin is anywhere). I modified it as follows, which works, by specifying NOT EQUAL to 'X' (essentially anything else). But I'm wondering if there is a cleaner more efficient way to do this? What if I wanted to also add an OR operator (e.g. type = Sports or SUV)?

%LET type_name = type = 'Sports';
%LET type_name = type ~= 'X';                  /* Any type */
%LET origin_location = origin = 'Europe';
%LET origin_location = origin ~= 'X';          /* Any origin */
%MACRO Printcars;
PROC PRINT DATA = sashelp.cars;
WHERE &type_name and &origin_location;
RUN;
%MEND Printcars;
%Printcars

[Tom]

If you are going to use the macro variable value in a macro then you can include macro logic to adjust the code generated to match the type of value passed.  So you might want to pass it a list of values and use it with the IN operator.  You could conditionally generate the WHERE condition only when the list is not empty.

Plus if you are going to want to pass values to a macro go ahead and use parameters for the macro instead of having "magic" macro variable references that appear in the middle of the macro definition without any explanation of where the value is supposed to have come from.

%macro Printcars(type,origin);
proc print data = sashelp.cars;
%if %length(&type) %then %do;
  where also type in (&type);
%end;
%if %length(&origin) %then %do;
  where also origin in (&origin);
%end;
run;
%mend Printcars;

Example calls:

1435  %printcars('Sports','Europe')
MPRINT(PRINTCARS):   proc print data = sashelp.cars;
MPRINT(PRINTCARS):   where also type in ('Sports');
NOTE: WHERE clause has been augmented.
MPRINT(PRINTCARS):   where also origin in ('Europe');
NOTE: WHERE clause has been augmented.
MPRINT(PRINTCARS):   run;

NOTE: There were 23 observations read from the data set SASHELP.CARS.
      WHERE (type='Sports') and (origin='Europe');
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.03 seconds
      cpu time            0.01 seconds


1436  %printcars(origin='Europe')
MPRINT(PRINTCARS):   proc print data = sashelp.cars;
MPRINT(PRINTCARS):   where also origin in ('Europe');
NOTE: WHERE clause has been augmented.
MPRINT(PRINTCARS):   run;

NOTE: There were 123 observations read from the data set SASHELP.CARS.
      WHERE origin='Europe';
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.08 seconds
      cpu time            0.06 seconds
1437  %printcars(origin='Europe',type='SUV' 'Wagon')
MPRINT(PRINTCARS):   proc print data = sashelp.cars;
MPRINT(PRINTCARS):   where also type in ('SUV' 'Wagon');
NOTE: WHERE clause has been augmented.
MPRINT(PRINTCARS):   where also origin in ('Europe');
NOTE: WHERE clause has been augmented.
MPRINT(PRINTCARS):   run;

NOTE: There were 22 observations read from the data set SASHELP.CARS.
      WHERE type in ('SUV', 'Wagon') and (origin='Europe');
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.03 seconds
      cpu time            0.03 seconds

If you want to exclude some cases then perhaps make a different parameter.  For example you might add an XTYPE parameter where you could list the types you want to exclude.

%macro Printcars(type,origin,xtype);
proc print data = sashelp.cars;
%if %length(&type) %then %do;
  where also type in (&type);
%end;
%if %length(&origin) %then %do;
  where also origin in (&origin);
%end;
%if %length(&xtype) %then %do;
  where also not type in (&xtype);
%end;
run;
%mend Printcars;

[PaigeMiller]

How about this? I use the %qlist macro to help format the text strings in quotes and separated by commas.

%macro printcars(type=,origin=);
proc print data = sashelp.cars;
where 
    %if &type^= %then type in %qlist(&type); 
    %if &type^= and &origin^= %then and;
    %if &origin^= %then origin in %qlist(&origin); 
    ;
run;
%mend printcars;

options mprint;
%printcars(type=Sports,origin=Asia Europe)
%printcars(origin=Asia Europe)
%printcars(type=Sports)
%printcars(type=Sports Sedan)
%printcars(type=Sports Moose)
%printcars()

[ballardw]

First I will make a style comment that may save you a LOT of headaches in the long run. Having a non-SAS automatic macro variable appear in the middle of a macro definition leads to all sorts of questions about "where does that come from and what might it contain" when reviewing code that has the macro calls and definition but possibly not the %let statements or other code that generates the macro variables. So you would be better off providing those variables as Parameters to the macro such as:

%MACRO Printcars(type_name=, origin_location=);
   PROC PRINT DATA = sashelp.cars;
   WHERE type = &type_name and origin = &origin_location;
   RUN;
%MEND Printcars;

/* and use Note the above definition will not work with this call
but for the point about parameters it is an example*/
%printcars(type_name = Sports, Origin_location = Europe);

The second thing to carefully look at how you want to generate code. Do you ALWAYS subset to a Type_name or origin (your question suggests no), or even do you always want a WHERE statement at all?

Ideally the macro code would determine this from the parameters passed OR require an additional parameter to force the use of the Where statement, a style choice depending on the actual use of a macro.

You can parse the values of your type_name and/or origin_location values to indicate what to do.

But first consider the types of code you want to generate. Hint: passing quotes or having quotes in macro variables often can be a mess in the long run.

Here is one way, with my take on making a more flexible macro:

%MACRO Printcars(type_name=, origin_location=);
   PROC PRINT DATA = sashelp.cars;
   %if %length(&type_name.)>0 or %length(origin_location)>0 %then %do;
   /* a where clause is requested*/
   where
      %if %sysfunc(countw(&type_name.))>0 %then %do;
      /* need a type name comparison*/
      type in (
         %do i= 1 %to %sysfunc(countw(&type_name.));
         /* uses default list of separators for possible multiple types*/
           %let t = %scan(&type_name.,&i.); 
          /*place the type inside the parntheses of the IN operator*/
           "&t."
         %end;
         /*close the parentheses for the In operator*/
          )
         /* if origin is needed place "and" after the type list*/
          %if %length(origin_location)>0 %then %do;
          AND
          %end;
      %end;/* of type*/
      /* similar for origin_location*/
      %if %sysfunc(countw(&origin_location.))>0 %then %do;
      /* need a type name comparison*/
      origin in (
         %do i= 1 %to %sysfunc(countw(&origin_location.));
         /* uses default list of separators for possible multiple types*/
           %let t = %scan(&origin_location.,&i.); 
          /*place the type inside the parntheses of the IN operator*/
           "&t."
         %end;
         /*close the parentheses for the In operator*/
          )
      %end;/* of origin*/
      /* next ends the where clause when needed*/
      ;
   %end;
   run;
/*   WHERE type = &type_name and origin = &origin_location;*/
/*   RUN;*/
%MEND Printcars;

options mprint;
%Printcars(type_name=Sports SUV, origin_location=Europe)
options nomprint;

Note that I turned on the  MPRINT option to show what the generated code looks like.

I hope the comments help with why each piece is there.

General comment with macro code learning: Save the code before running it! It is very easy with macro code errors to generate stuff that renders the SAS system hard to recover from. Symptoms like "nothing happening" or code in the log with nothing running are common depending on which unclosed quote or parentheses might be involved. Mismatched "  '  are another issue.

A more generic approach would be the make the data set a parameter.

You can also make a parameter like Parm= type="Sports" but you still have to check whether you have provided a value for it and whether an Where statement should be generated (plus the origin). What might happen with the longer form of where condition is that you may end up using characters that require macro quoting such a comma, & (consider a company name value like "Jones & Sons"), % and others. Which can be a tad sticky.

[FreelanceReinh]

---

@DougHold wrote:

I would also like to be able to specify 'anything'. For example, I want the type to be any type, but origin is Europe (or type is sports and origin is anywhere). I modified it as follows, which works, by specifying NOT EQUAL to 'X' (essentially anything else). But I'm wondering if there is a cleaner more efficient way to do this?

Just to add the answer to the particular question above:

I always use 1 (meaning the Boolean value TRUE) in such cases, i.e., you would specify

%LET type_name = 1;                  /* Any type */
%LET origin_location = 1;          /* Any origin */

in your second example.

[Quentin · Accepted solution]

One option for allowing a macro user to specify 'anything' for a where condition is to design the macro so that they pass the where condition, rather than a list of values, e.g.:

%MACRO Printcars(where=);
PROC PRINT DATA = sashelp.cars;
  %if %length(&where) %then %do ;
    where &where ;
  %end ;
RUN;
%MEND Printcars;

%Printcars()
%Printcars(where=(type='Sports'))
%Printcars(where=(type='Sports' and origin='Europe'))
%Printcars(where=(type='Sports' or origin='Asia'))

Of course in most macros, the name of the input dataset will be a parameter rather than hardcoded, and often that is enough allow the user to pass a where condition, e.g.:

%MACRO tPrint(data=);
PROC PRINT DATA = &data;
RUN;
%MEND tPrint;

%tPrint(data=sashelp.cars)
%tPrint(data=sashelp.cars(where=(type='Sports')))
%tPrint(data=sashelp.cars(where=(type='Sports' and origin='Europe')))
%tPrint(data=sashelp.cars(where=(type='Sports' or origin='Asia')))

For macros that will often have a macro call written by a person, I find the above handy. 

[PaigeMiller]

@Quentin this is a very good solution, and certainly much more general than my macro ... your case can also handle where cylinders=4. My macro, in answering the original very specific question, can't handle filtering on anything other than type and region, and can't really be made as general as yours. So except for learning purposes, I recommend that my macro not be used.

[DougHold]

Thank you everyone for the replies. This was all super helpful for learning about macros and how to make my code more dynamic 🙂