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

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

1 ACCEPTED SOLUTION

Accepted Solutions
user24feb
Barite | Level 11

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

10 REPLIES 10
Astounding
PROC Star

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__
Jade | Level 19

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
Super User
%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
Diamond | Level 26 RW9
Diamond | Level 26

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
Barite | Level 11

Replace the Where-line with:

 

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

FreelanceReinh
Jade | Level 19

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_
Fluorite | Level 6

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
PROC Star

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
Barite | Level 11

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_
Fluorite | Level 6

Thank you everyone for your input again!

 

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

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
  • 10 replies
  • 4664 views
  • 3 likes
  • 7 in conversation