topic Where Option in set statement in SAS Programming

Where Option in set statement

ArpitSharma — Fri, 06 Oct 2017 21:23:02 GMT

Hello,

So I have this below working code working:

%macro where_clause(where=);
	%if &where.= %then %do;
		%let where_optn= ;
	%end;
	%else %do;
		%let where_optn=(where=(&where.)) ;
	%end;
data class;
set sashelp.class &where_optn.;
run;
%mend;

%where_clause;
%where_clause(where=name="Alice" );

%where_clause(where=name="Alice" and sex="F" );

The same code would not run if I invoke the macro as:

%where_clause(where=name in ("Alice") );

Now I understand that it is because of parenthesis. I want the user to be able to pass more conditions using and/or.

Please advise.

Thanks

Re: Where Option in set statement

ballardw — Fri, 06 Oct 2017 21:36:47 GMT

You will need to consider all of the cases of the garbage your users may through at your macro.

%where_clause(where=%str(name="Alice") );

%str will keep some elements from being seen by the macro parser. But you will need to consider different approaches people are going to attempt to pass Macro elements such as % or & .

%nrstr will mask other characters from interpretation

Please see the documentation for these functions as well as %quote, %nrquote, %unquote and %superq and "Macro masking" This is not a trivial issue.

Re: Where Option in set statement

Reeza — Fri, 06 Oct 2017 21:46:25 GMT

Does it have to be that complicated?

%macro where_clause(cFilter=);
%if &cFilter=%str() %then %let cFilter=1=1;

data class;
set sashelp.class (where=(&cFilter));
run;

%mend;

%where_clause(cFilter=);
%where_clause(cfilter=name="Alice" );
%where_clause(cFilter=name="Alice" and sex="F" );

I changed the parameter name to help avoid confusion both from a programmer side and in interpreting the model.

Re: Where Option in set statement

Tom — Fri, 06 Oct 2017 21:48:04 GMT

I don't understand your problem.

The code you posted should work as long as the users pass in valid syntax. You will not even need any macro quoting as anything that is valid inside of a WHERE=(....) dataset option will be valid to pass into the macro.

Do you have examples of things that do not work?

Re: Where Option in set statement

Reeza — Fri, 06 Oct 2017 21:48:58 GMT

@Tom there's too many WHEREs in the code. In the parameter and the data step filter.

Re: Where Option in set statement

ArpitSharma — Fri, 06 Oct 2017 21:48:59 GMT

This will fail for::
%where_clause(cFilter=name in ("Alice"));

Re: Where Option in set statement

Reeza — Fri, 06 Oct 2017 21:52:06 GMT

@ArpitSharma wrote:
This will fail for::
%where_clause(cFilter=name in ("Alice"));

~~Nope.~~

371 %where_clause(cfilter=name="Alice" );

NOTE: There were 1 observations read from the data set
SASHELP.CLASS.
WHERE name='Alice';
NOTE: The data set WORK.CLASS has 1 observations and 5 variables.'

NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds

EDIT: My bad, you're right.

Re: Where Option in set statement

ArpitSharma — Fri, 06 Oct 2017 21:50:42 GMT

I have provided the example that does NOT work.
It does NOT work for::
%where_clause(where=name in ("Alice") );

Re: Where Option in set statement

ArpitSharma — Fri, 06 Oct 2017 21:52:14 GMT

I am using the IN operator.

Re: Where Option in set statement

Tom — Fri, 06 Oct 2017 21:59:00 GMT

It is not the SAS code that is failing it is the invalid %IF condition.

I would recode it this way. There are slightly more robust methods to test for empty parameters, but %LENGTH() works for 99% of real cases.

%macro where_clause(where=);
%local dsnopt;
%if %length(&where) %then %let dsnopt=where=(&where);
data class;
  set sashelp.class (&dsnopt);
run;
%mend where_clause;

Re: Where Option in set statement

s_lassen — Mon, 09 Oct 2017 11:09:58 GMT

@ArpitSharma:

A simple solution that will work in most cases:

%macro where_clause(where=);
%if %length(&where) %then
  %let where=(where=(&where));
data class;
set sashelp.class &where;
run;
%mend;

Re: Where Option in set statement

andreas_lds — Mon, 09 Oct 2017 16:44:04 GMT

Would add
%local where;
after %macro statement. To avoid funny error if the variable where is used outside of where_clause, too.

Re: Where Option in set statement

Quentin — Mon, 09 Oct 2017 16:52:39 GMT

@andreas_lds, because the macro variable WHERE is defined as a parameter to the macro, it is automatically local.

1    %let where=0;
2    %macro test(where=);
3      %put _user_;
4    %mend test;
5
6    %test(where=1)
TEST WHERE 1
GLOBAL WHERE 0

I imagine some people might have the practice of declaring all macro variables that are local on a %LOCAL statement, including parameters, but it's not necessary to include the parameters.

Other than parameters, I'm a zealot about the %LOCAL statement. Nothings bugs me more than to see a post or worse yet paper where macro programmers ignore the need to define the scope of macro variables they create.

Re: Where Option in set statement

andreas_lds — Mon, 09 Oct 2017 20:15:35 GMT

@Quentin wrote:

@andreas_lds, because the macro variable WHERE is defined as a parameter to the macro, it is automatically local.

Don't know why i haven't noticed that WHERE is a macro parameter. Thanks for clarifying my post.