Passing a comma delimited variable to a Macro

[KevinC_]

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

[Astounding]

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.

[data_null__]

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

[Ksharp]

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

[RW9]

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.

[user24feb]

Replace the Where-line with:

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

[FreelanceReinh]

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

[KevinC_]

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!

[Astounding]

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.

[user24feb · Accepted solution]

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.));

[KevinC_]

Thank you everyone for your input again!

I used user24feb's suggestion and it worked for me.  Thank you user24feb! :)