DATA Step, Macro, Functions and more

Where Option in set statement

Accepted Solution Solved
Reply
Contributor
Posts: 38
Accepted Solution

Where Option in set statement

[ Edited ]

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


Accepted Solutions
Solution
‎10-10-2017 05:01 PM
PROC Star
Posts: 226

Re: Where Option in set statement

Posted in reply to ArpitSharma

@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;

View solution in original post


All Replies
Super User
Posts: 13,003

Re: Where Option in set statement

Posted in reply to ArpitSharma

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.

Super User
Posts: 22,820

Re: Where Option in set statement

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.

Contributor
Posts: 38

Re: Where Option in set statement

This will fail for::
%where_clause(cFilter=name in ("Alice"));
Super User
Posts: 22,820

Re: Where Option in set statement

[ Edited ]
Posted in reply to ArpitSharma

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. 

Contributor
Posts: 38

Re: Where Option in set statement

I am using the IN operator.
Super User
Super User
Posts: 7,845

Re: Where Option in set statement

[ Edited ]
Posted in reply to ArpitSharma

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;

 

Super User
Super User
Posts: 7,845

Re: Where Option in set statement

Posted in reply to ArpitSharma

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?

Super User
Posts: 22,820

Re: Where Option in set statement

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

Contributor
Posts: 38

Re: Where Option in set statement

I have provided the example that does NOT work.
It does NOT work for::
%where_clause(where=name in ("Alice") );
Solution
‎10-10-2017 05:01 PM
PROC Star
Posts: 226

Re: Where Option in set statement

Posted in reply to ArpitSharma

@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;
Super Contributor
Posts: 498

Re: Where Option in set statement

Posted in reply to ArpitSharma
Would add
%local where;
after %macro statement. To avoid funny error if the variable where is used outside of where_clause, too.
PROC Star
Posts: 1,426

Re: Where Option in set statement

Posted in reply to andreas_lds

@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.

Super Contributor
Posts: 498

Re: Where Option in set statement


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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 198 views
  • 4 likes
  • 7 in conversation