topic Generating tables based on conditional? in SAS Programming

Generating tables based on conditional?

bradbelf262 — Mon, 04 May 2015 16:39:16 GMT

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

Re: Generating tables based on conditional?

Astounding — Mon, 04 May 2015 17:45:17 GMT

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.

Re: Generating tables based on conditional?

bradbelf262 — Mon, 04 May 2015 17:58:15 GMT

Thank you very much!