Generating tables based on conditional?

[bradbelf262]

I am trying to write a macro that will create a new table dependent on certain criteria. The user can enter a single number or a range of numbers to filter the original data on.

For example, if the starting data set is shown below, the end user can choose to filter Col Z by a single number (ie: where Z=1) or a range of numbers (ie: where Z is between 1 and 5).

  X    Y    Z

    0    1    3

    2    7    9

    0     0   0

    2     1   1

If the user wants to enter a range of numbers to filter on, there will be values populated for &start_range and &end_range. If the user wants to filter on a single number, &end_range will equal null.

I want code that will check if &end_range equals null. If it does, the where statement should create a new table for all rows where column Z=&start_range. If &end_range is NOT equal to null (it has a value populated), the where statement will create a new table for all rows where column Z is between &start_range and &end_range.

For a single value, (where &start_range=1 and &end_range=’ ‘), the resulting table would be:

    X    Y    Z

    2     1   1

For a range of values, (where &start_range=1 and &end_range=5), the resulting table would be:

    X    Y    Z

    0    1    3

    2     1   1

This is the code I have so far, not 100% sure if syntax is correct so open to any and all suggestions, thanks!!:

     %macro filter();

    proc sql;  

                create table filtered_data AS

                select raw_data.*

                from raw_data

                WHERE      

                      %if &end_range NE '' %then %do;

                            Z between '&start_range' AND '&end_range';

                      %end;

                      %else %do;

                            Z = '&start_range';

                      %end;

                quit;

    %mend;

    %filter();

[Astounding · Accepted solution]

There are a few corrections to your syntax.  I'm assuming that you are mainly concerned with macro language syntax, and that you know what the SQL code should look like.

There are a few ways to check whether a macro variable has a missing value.  (An entire paper has been written comparing various methods.)  The one you have chosen doesn't work with macro language:

%if &end_range NE '' %then %do;

The one I prefer (simply, easy to interpret, does the job, quick) is this:

%if %length(&end_range) %then %do;

When referring to macro variables, never use single quotes (they suppress all macro activity).  Double quotes might be appropriate:

Z = "&start_range";

But be aware that the quotes become part of the SELECT statement.  So they would only be appropriate if the underlying variable Z is a character variable.  If Z is numeric, just lose the quotes:

Z = &start_range;

The same comment applies to the range when &end_range is non-blank.

Good luck.

[bradbelf262]

Thank you very much!