topic Re: Passing a comma delimited variable to a Macro in SAS Programming

Passing a comma delimited variable to a Macro

KevinC_ — Wed, 03 Feb 2016 22:34:07 GMT

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

Re: Passing a comma delimited variable to a Macro

Astounding — Wed, 03 Feb 2016 22:38:25 GMT

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.

Re: Passing a comma delimited variable to a Macro

data_null__ — Wed, 03 Feb 2016 23:07:13 GMT

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

Re: Passing a comma delimited variable to a Macro

Ksharp — Thu, 04 Feb 2016 01:52:22 GMT

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

Re: Passing a comma delimited variable to a Macro

RW9 — Thu, 04 Feb 2016 09:13:32 GMT

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.

Re: Passing a comma delimited variable to a Macro

user24feb — Thu, 04 Feb 2016 09:19:28 GMT

Replace the Where-line with:

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

Re: Passing a comma delimited variable to a Macro

FreelanceReinh — Thu, 04 Feb 2016 13:51:32 GMT

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

Re: Passing a comma delimited variable to a Macro

KevinC_ — Thu, 04 Feb 2016 14:51:02 GMT

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!

Re: Passing a comma delimited variable to a Macro

Astounding — Thu, 04 Feb 2016 15:16:38 GMT

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.

Re: Passing a comma delimited variable to a Macro

user24feb — Thu, 04 Feb 2016 15:22:40 GMT

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

Re: Passing a comma delimited variable to a Macro

KevinC_ — Thu, 04 Feb 2016 21:10:41 GMT

Thank you everyone for your input again!

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