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();
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.
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.
Thank you very much!
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.