Generating tables based on conditional?

Solved
Occasional Contributor
Posts: 16

Generating tables based on conditional?

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();

Accepted Solutions
Solution
‎05-04-2015 01:45 PM
Super User
Posts: 6,785

Re: Generating tables based on conditional?

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.

All Replies
Solution
‎05-04-2015 01:45 PM
Super User
Posts: 6,785

Re: Generating tables based on conditional?

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.

Occasional Contributor
Posts: 16