CODEWITHSUNDEEP
regexsas
Petr91
Petr91

Reputation: 43

SAS remove the specified word when it starts or ends the expression

I am writing a macro variable that aims at producing a valid SQL WHERE clause as specified by user in prompts. Assume we have 3 variables X, Y, Z, and so on. User may specify the filter for every variable and macro variable looks like:

a = x eq 1 and y eq 1 and z eq 1;

which is then proceeded to the WHERE clause. But if user specifies only, let's say' filter for Y it looks like:

a =  and y eq 1 and

And I would like it to look like:

a = y eq 1

That is why I would like to somehow remove the operand 'AND' when it starts or ends the expression (it may start or end it multiple times, e.g. if we fitler only Z variable it looks like):

a =  and and and z eq 1

I suppose it could be easily done with regular expressions but since I'm new to it, is there anyone willing to help me ? ;)

Upvotes: 3

Views: 86

Answers (5)

Petr91
Petr91

Reputation: 43

Thanks all, I have already figured out a similar structure as @Robert Penridge, but I appriciate all of the answers :) Thanks!

PS. I also was not familiar with WHERE ALSO - may prove useful in the future:)

Upvotes: 0

Robert Penridge
Robert Penridge

Reputation: 8513

Slight rework of @DirkHorsten's technique. This simply organizes the code in a slightly different manner so that the SQL statement can be more easily read. In my opinion, the SQL statement is the important piece of code that you would like readers to understand (so let's keep it simple!), while the building of the where clause is just a side-note. This can be a valuable approach, especially as your SQL statements become more complex.

Approach 1, a 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, individual 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;

Upvotes: 2

Joe
Joe

Reputation: 63434

Assuming you're doing this via macro parameters, this is easier to do by supplying a 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 a left-out argument.

If you only want to pass values (not the full equality), then 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 true in SAS, but if you're passing through to SQL Server or Oracle and might have nulls this will not work (as null=null is false in SQL Server or Oracle).

Upvotes: 2

Dirk Horsten
Dirk Horsten

Reputation: 3845

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

Upvotes: 1

Vasilij Nevlev
Vasilij Nevlev

Reputation: 1449

You can use a little known "where also" expression. Appended "where also" expressions logically equal to AND operator for each WHERE clause and you can user "where also" as your first WHERE clause without any issues to your code.

If you have a macro like that: 

%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 to someting like:

%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 you test the code, you need to at least initialise the macro variables. You could do it with something like that: 

%IF %symexist(&Z)=0 %THEN %LET Z = ;

Upvotes: 0

Related Questions