How to use "AND" operators in SAS Macro?

Question

I recently coded a macro to create multiple "Proc freqs" easily, having to enter simply the variables and the condition "where" (if there is one). It generally works, still I've been struggling to make it work when my condition "where" contains an "AND".

Here's the macro:

options mprint minoperator;

%macro check(var1=,var2=,var3=, cond=);

Proc freq data= &dataset_full.; 
Table &var1.
    %if &var2. NE %str() %then 
     * &var2. ;
    %if &var3. NE %str()  %then 
    * &var3. ;
    / out="&var1. &var2. &var3." norow nocol nopercent;
    %if &cond. NE %str() %then
    &cond.;
    ;
Run;

%mend;

For instance, I could write %check(var1=age,var2=weight, cond=where age=1 and weight=10). There would be no error, it just wouldn't apply my condition to the output. It's like "cond" was empty according to MPRINT output. Conversely, if my condition contains "OR", it works fine.

I have tried using str(), %eval but it doesn't work.

Answer 1

If you use a data set where= option and force the user to pass the conditions in a parenthetical expression (same as what where= requires), macro will figure it out

Example:

The default =(1) will select all rows.

%macro freq(data=,var1=,var2=,var3=,where=(1));

  proc freq noprint data=&data(where=&where);
    table &var1
    %if &var2. NE %str() %then 
       * &var2. ;
    %if &var3. NE %str()  %then 
      * &var3. ;
    / out="&var1._&var2._&var3." norow nocol nopercent;
      ;
  run;

%mend;

options mprint;

%freq(data=sashelp.cars, var1=type)
%freq(data=sashelp.cars, var3=enginesize, var1=type)
%freq(data=sashelp.cars, var2=origin, var3=drivetrain, var1=type)
%freq(data=sashelp.cars, var2=origin, var3=drivetrain, var1=type
     ,where=(origin='Asia' and drivetrain='All' and msrp<50000))
            ^.................................................^

Answer 2

2 points

1. You should probably do something about the out= option on the Tables Statement. You have to specify a SAS data set name (not in ""). I guess you want to specify your input variables in the name. See the code below.
2. The code below works, when masking the cond= value with the %Str Macro Function

Try this

options mprint minoperator;

%macro check(var1=,var2=,var3=, cond=);

Proc freq data= &dataset_full.; 
Table &var1.
    %if &var2. NE %str() %then 
     * &var2. ;
    %if &var3. NE %str()  %then 
    * &var3. ;
    / out= freq_&var1.&var2.&var3. norow nocol nopercent;
    %if &cond. NE %str() %then
    &cond.;
    ;
Run;

%mend;

%let dataset_full = sashelp.class;

%check(var1=age,var2=weight, cond=%str(where age=1 and weight=10));

Answer 3

The macro processor is probably having an issue with your %IF statement(s).

Use a different test to check if a parameter is populated.

The easiest test is to look for a non zero length. Also the WHERE statement does not care if you give it an empty condition, so no need to test &COND at all.

proc freq noprint data=&data;
  table &var1
%if %length(&var2) %then * &var2 ;
%if %length(&var3) %then * &var3 ; 
    / out=&var1._&var2._&var3. norow nocol nopercent;
  ;
  where &cond ;
run;

You also might have trouble with the OUT dataset name if the variable names are longer than an average of 10 bytes.