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();
... View more