SAS removes the specified word when it starts or ends an expression
I am writing a macro that aims to generate the correct SQL WHERE clause as specified by the user in prompts. Suppose we have 3 variables X, Y, Z, etc. The user can specify a filter for each variable, and the macro variable looks like this:
a = x eq 1 and y eq 1 and z eq 1;
which then goes to the WHERE clause. But if the user only specifies, let the "filter for Y" look like this:
a = and y eq 1 and
And I would like it to look like this:
a = y eq 1
This is why I would like to somehow remove the "AND" operand when it starts or ends the expression (it can start or end multiple times, for example if we are only for variable Z only for variable):
a = and and and z eq 1
I suppose this could be easily done with regex, but since I'm new to this, is there anyone who wants to help me?;)
source to share
Minor rework of @DirkHorsten technique. It just arranges the code slightly differently so that the SQL statement can be read easily. In my opinion, the SQL statement is an important piece of code that you would like your readers to understand (so keep it simple!), While creating a where clause is just a side note. This can be a valuable approach, especially as your SQL queries get more complex.
Approach 1, single variable for all filters:
%macro getMyData(xValue=, yValue=, zValue=);
%local and_filters;
* THE BORING IMPLEMENTATION DETAILS ARE KEPT SEPARATE;
%let and_filters = ;
%if "&xValue" ne "" %then %do;
%let and_filters = &and_filters and sex eq "&xValue";
%end;
%if "&yValue" ne "" %then %do;
%let and_filters = &and_filters and age eq &yValue;
%end;
%if "&zValue" ne "" %then %do;
%let and_filters = &and_filters and height eq &zValue;
%end;
* THE IMPORTANT PIECE OF CODE IS EASY TO READ;
proc sql;
select *
from sashelp.class
where 1 &and_filters
;
quit;
%mend;
Approach 2, separate variables for each filter:
%macro getMyData(xValue=, yValue=, zValue=);
%local and_sex_filter and_age_filter and_height_filter;
* THE BORING IMPLEMENTATION DETAILS ARE KEPT SEPARATE;
%let and_sex_filter = ;
%let and_age_filter = ;
%let and_height_filter = ;
%if "&xValue" ne "" %then %do;
%let and_sex_filter = and sex eq "&xValue";
%end;
%if "&yValue" ne "" %then %do;
%let and_age_filter = and age eq &yValue;
%end;
%if "&zValue" ne "" %then %do;
%let and_height_filter = and height eq &zValue;
%end;
* THE IMPORTANT PIECE OF CODE IS EASY TO READ;
proc sql;
select *
from sashelp.class
where 1
&and_sex_filter
&and_age_filter
&and_height_filter
;
quit;
%mend;
source to share
Assuming you are doing this with macros, it is easier to do this by specifying the default.
%macro filter(x=1,y=1,z=1);
where &x. and &y. and &z.;
%mend filter;
1 is true, so it acts (along with AND) as the left-out argument.
If you want to pass values (rather than full equality), you can also do this:
%macro filter(x=x, y=y, z=z);
where x=&x. and y=&y. and z=&z.;
%mend filter;
x=x
is always correct in SAS, but if you go to SQL Server or Oracle and can have zeros, it won't work (as it null=null
is false in SQL Server or Oracle).
source to share
%macro getMyData(xValue=, yValue=, zValue=);
proc sql;
select *
from sashelp.class
where
%if %length(&xValue) %then %do;
sex = "&xValue." and
%end;
%if %length(&yValue) %then %do;
age = &yValue. and
%end;
%if %length(&zValue) %then %do;
height >= &zValue. and
%end;
1;
quit;
%mend;
Title 'Females';
%getMyData(xValue=F);
Title '12 year';
%getMyData(yValue=12);
Title 'Large males';
%getMyData(xValue=M, zValue=60);
source to share
You can use the little known "where also" expression. Added "where also" clauses that logically equal the AND operator for each WHERE clause, and you can use "where also" as your first WHERE clause without any problem with your code.
If you have a macro like this:
%MACRO get_data;
data want;
set have;
where a = x eq 1 and y eq 1 and z eq 1;
RUN;
%MEND;
You can rewrite the following code:
% MACRO get_data;
data want;
set have;
%IF &X ne %THEN
%DO;
where also &x eq 1;
%END;
%IF &Y ne %THEN
%DO;
where also &y eq 1;
%END;
%IF &Z ne %THEN
%DO;
where also &z eq 1;
%END;
RUN;
%MEND;
Before testing your code, you need to at least initialize macro variables. You could do it something like this:
%IF %symexist(&Z)=0 %THEN %LET Z = ;
source to share