BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bradbelf262
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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.

View solution in original post

2 REPLIES 2
Astounding
PROC Star

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
Calcite | Level 5

Thank you very much!

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 528 views
  • 0 likes
  • 2 in conversation