DATA Step, Macro, Functions and more

Passing a comma delimited variable to a Macro

Accepted Solution Solved
Reply
Regular Contributor
Posts: 173
Accepted Solution

Passing a comma delimited variable to a Macro

Hello Everyone,

 

I am trying to pass a comma delimited macro variable to a macro. 

I get an error that says " ERROR: More positional parameters found than defined. "  Does anyone have any suggestions on how to make this macro work?

 

Thank you very much in advance.

 

%MACRO test(cd);

Proc SQL;

Create Table AAA as

SELECT DISTINCT Customer_ID, amount

FROM Cust_Transaction

Where (Trans_Type_CD in (&cd.))

;QUIT;

%MEND;

 

%let Trans_cd = 1387-D, 1419-D, 1383-D;

 

%test(&Trans_cd);


Accepted Solutions
Solution
‎02-04-2016 04:09 PM
Super Contributor
Posts: 340

Re: Passing a comma delimited variable to a Macro

Hm, you are right. This code will work (but if it was my code, I would not define Trans_cd with a comma in the first place):

 


Data Cust_Transaction;
  Length Trans_Type_CD $6.;
  Trans_Type_CD='DFEE'; Output;
  Trans_Type_CD='1387-D'; Output;
  Trans_Type_CD='1419-D'; Output;
Run;

%MACRO test(cd);
%Put **CHECK**&cd.**;
Proc SQL;
Create Table AAA as
SELECT DISTINCT *
FROM Cust_Transaction
Where (Trans_Type_CD in (%Sysfunc(TranWrd("&cd.",%Str(, ),%Str(", "))))) 
;QUIT;
%MEND;
 
%let Trans_cd = 1387-D, 1419-D, 1383-D; 

%test(%BQuote(&Trans_cd.));

View solution in original post


All Replies
Super User
Posts: 5,503

Re: Passing a comma delimited variable to a Macro

There are hoops you can jump through, using macro quoting functions.  But a better idea (in my opinion, of course) is to just pass the name of the macro variable:

 

%MACRO test(cd);

Proc SQL;

Create Table AAA as

SELECT DISTINCT Customer_ID, amount

FROM Cust_Transaction

Where (Trans_Type_CD in (&&&cd..))

;QUIT;

%MEND;

 

%let Trans_cd = 1387-D, 1419-D, 1383-D;

 

%test(Trans_cd)

 

The three ampersands inside the macro definition resolve &&&cd.. into &Trans_cd. which then re-resolves into your list of values.

Respected Advisor
Posts: 3,799

Re: Passing a comma delimited variable to a Macro

Parenthesis of course.

34         %MACRO test(cd);
35            %put NOTE: &=cd;
36            %let cd = %sysfunc(scan(%superq(cd),1,%str(%)%(),Q));
37            %put NOTE: &=cd;
38            %MEND;
39         
40         %let Trans_cd = 1387-D, 1419-D, 1383-D;
41         
42         %test((&Trans_cd))
NOTE: CD=(1387-D, 1419-D, 1383-D)
NOTE: CD=1387-D, 1419-D, 1383-D
Super User
Posts: 10,023

Re: Passing a comma delimited variable to a Macro

%MACRO test(cd);
Proc SQL;
Create Table AAA as
SELECT *
FROM sashelp.class
Where findw("&Trans_cd",strip(sex))
;QUIT;
%MEND;
 
%let Trans_cd = F MM ;
 
%test(&Trans_cd)

Change your code. You are searching a character variable.

Super User
Super User
Posts: 7,942

Re: Passing a comma delimited variable to a Macro

Or you could just use Base SAS, put your "data" parameters in a "data" set, and then use that:

proc sql;
  create table AAA as
  select  distinct CUSTOMER_ID,
          AMOUNT
  from    CUST_TRANSACTION
  where   TRANS_TYPE_CD in (select distinct CD from PARAMETER_DATASET);
quit;

No need for any macro language, just plain simple Base SAS coding, and the full functionality of that language.

Super Contributor
Posts: 340

Re: Passing a comma delimited variable to a Macro

Replace the Where-line with:

 

Where (Trans_Type_CD in (%Sysfunc(TranWrd("&cd.",%Str(, ),%Str(", ")))))

Trusted Advisor
Posts: 1,117

Re: Passing a comma delimited variable to a Macro

Hello @KevinC_,

 

You could mask the commas with the %STR function: %let Trans_cd = %str(1387-D, 1419-D, 1383-D);
However, the more serious issue are the missing quotes around your character constants 1387-D etc. (see user24feb's post). Also, the IN operator doesn't require commas, but accepts blanks as separators as well.


So, you could resolve both issues at once by simply writing:

%let Trans_cd = '1387-D' '1419-D' '1383-D';

 

Regular Contributor
Posts: 173

Re: Passing a comma delimited variable to a Macro

Posted in reply to FreelanceReinhard

Thank you all for your help!

 

I tried some of your suggestions and changed my Where statement  as below :

 

WHERE (Trans_Type_CD in (&&&tran_cd.))

 

Where (Trans_Type_CD in (%Sysfunc(TranWrd("&tran_cd.",%Str(, ),%Str(", ")))))

 

The rest of the query remains the same.  I got this error:

ERROR: All positional parameters must precede keyword parameters

 

Any suggestions?

 

Thank you!

 

Super User
Posts: 5,503

Re: Passing a comma delimited variable to a Macro

Two issues regarding my suggestion ...

 

First, to get the correct SAS syntax in a WHERE statement, you do have to add the quotes.  This won't work:

 

%let Trans_cd = 1387-D, 1419-D, 1383-D;

 

You need quotes added around a list of character values in an IN list.  Several variations are possible, this being one:

 

%let Trans_cd = "1387-D", "1419-D", "1383-D";

 

Second, you need to change the macro call.  It should use Trans_cd, not &Trans_cd:

 

%test (Trans_cd)

 

There are plenty of other workable suggestions here.  But these changes should get my &&& variation to work.

Solution
‎02-04-2016 04:09 PM
Super Contributor
Posts: 340

Re: Passing a comma delimited variable to a Macro

Hm, you are right. This code will work (but if it was my code, I would not define Trans_cd with a comma in the first place):

 


Data Cust_Transaction;
  Length Trans_Type_CD $6.;
  Trans_Type_CD='DFEE'; Output;
  Trans_Type_CD='1387-D'; Output;
  Trans_Type_CD='1419-D'; Output;
Run;

%MACRO test(cd);
%Put **CHECK**&cd.**;
Proc SQL;
Create Table AAA as
SELECT DISTINCT *
FROM Cust_Transaction
Where (Trans_Type_CD in (%Sysfunc(TranWrd("&cd.",%Str(, ),%Str(", "))))) 
;QUIT;
%MEND;
 
%let Trans_cd = 1387-D, 1419-D, 1383-D; 

%test(%BQuote(&Trans_cd.));
Regular Contributor
Posts: 173

Re: Passing a comma delimited variable to a Macro

Posted in reply to user24feb

Thank you everyone for your input again!

 

I used user24feb's suggestion and it worked for me.  Thank you user24feb! Smiley HappySmiley Happy

☑ This topic is solved.

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

Discussion stats
  • 10 replies
  • 598 views
  • 1 like
  • 7 in conversation