CODEWITHSUNDEEP
sassas-macro
hope288
hope288

Reputation: 1435

SAS macro parameter that is a list

I am trying to create a macro where one of the parameters is a list. My macro includes a proc sql with a where statement that has something like this:

Where Name in ('sarah','ben','adam')

I tried doing something like this:

%MACRO DATA_PULL (name=);
PROC SQL;
SELECT
FROM
Where Name in &name
;
QUIT;
%MEND DATA_PULL;

%DATA_PULL (Name=('sarah','ben','adam'))

but it doesn't work :( any help appreciated

Upvotes: 1

Views: 3296

Answers (4)

ocstl
ocstl

Reputation: 386

You can use the SYSPBUFF automatic macro variable, which contains the parameter values you supplied to the macro, including the parentheses and commas. The PARMBUFF option allows one to build a macro to deal with a varying number of parameters.

%macro data_pull / parmbuff;
    proc sql;
        select *
            from sashelp.class
                where name in &syspbuff.;
    quit;
%mend data_pull;

%data_pull('Alfred','Alice','ben','adam')

In this example, the SYSPBUFF variable is ('Alfred','Alice','ben','adam'), which fits nicely for your SQL query.

Upvotes: 1

Tom
Tom

Reputation: 51566

Trying to put commas in the value of the macro variable is the issue since comma is used to mark the transition between parameter values in the macro call.

What you posted is actually one way to allow the inclusion of commas in the value of a macro parameter. By enclosing the value inside or () the SAS compiler will know that the commas do not mark the start of new parameter values. If you fix your macro so that it generates a valid SELECT statement then it works.

%macro data_pull (name=);
proc sql;
select * from sashelp.class where name in &name;
quit;
%mend data_pull;

%data_pull(name=('Alfred','Alice','ben','adam'))

But the real solution is even easier. Just do not include the commas in the value to begin with. The IN operator does not need them. Then you can add the () in the macro code.

%macro data_pull (name=);
proc sql;
select * from sashelp.class where name in (&name);
quit;
%mend data_pull;

%data_pull(name='Alfred' 'Alice' 'ben' 'adam')

Or you can make your macro a little smarter and then the user can either include the () or not when calling the macro.

%macro data_pull (name=);
proc sql;
select * from sashelp.class
where name in (%scan(&name,1,(),q));
quit;
%mend data_pull;

Upvotes: 1

Kiran
Kiran

Reputation: 3315

you need to using macro quoting functions.

 %MACRO DATA_PULL (name=);
 PROC SQL;
 SELECT *
 FROM sashelp.class
 Where Name in &name
 ;
QUIT;

%MEND DATA_PULL;

%DATA_PULL (Name = %str(('Alfred', 'Alice', 'Barbara')))

Upvotes: 1

Richard
Richard

Reputation: 27498

The SAS in operator does not require commas. This is valid syntax:

where Name in ('sarah' 'ben' 'adam')

so you could have macro invocation with 

, names = ('sarah' 'ben' 'adam')

You can also pass commas in a macro parameter by properly quoting the value or value portion. In this case %str can be used.

, names = (%str('sarah','ben','adam'))

If you place the %str outside the in list parenthesis you may also want to escape the parenthesis within the %str

Some example invocations

%macro x(names=);

proc sql;
  create table want as
  select * from sashelp.class
  where name in &names
  ;

%mend;

%x(names=('Jane' 'James'))
%x(names=(%str('Jane', 'James')))
%x(names=%str(%('Jane', 'James'%)))

Upvotes: 1

Related Questions