BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ArpitSharma
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
s_lassen
Meteorite | Level 14

@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

13 REPLIES 13
ballardw
Super User

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.

Reeza
Super User

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.

ArpitSharma
Fluorite | Level 6
This will fail for::
%where_clause(cFilter=name in ("Alice"));
Reeza
Super User

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

ArpitSharma
Fluorite | Level 6
I am using the IN operator.
Tom
Super User Tom
Super User

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;

 

Tom
Super User Tom
Super User

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?

Reeza
Super User

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

ArpitSharma
Fluorite | Level 6
I have provided the example that does NOT work.
It does NOT work for::
%where_clause(where=name in ("Alice") );
s_lassen
Meteorite | Level 14

@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;
andreas_lds
Jade | Level 19
Would add
%local where;
after %macro statement. To avoid funny error if the variable where is used outside of where_clause, too.
Quentin
Super User

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

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
andreas_lds
Jade | Level 19

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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