BookmarkSubscribeRSS Feed
ren2010
Obsidian | Level 7
HI,

Here is my code to call a macro variable:

%let dd='tt','rr';


data gg;
input id name $2.;
datalines;
1 tt
2 rr
3 gh
;
run;

%macro file1(ts);
proc sql;
create table out as select * from gg where name in("&ts.");
quit;
%mend;

%file1(&dd.);

SAS is giving me an error saying multiple parameters in macro call,is there any
way to pass the value without this error.

Thanks in advance.
14 REPLIES 14
Ksharp
Super User
HI.
Your code should be like this:

[pre]
%let dd=%str('tt','rr');


data gg;
input id name $2.;
datalines;
1 tt
2 rr
3 gh
;
run;

%macro file1(ts);
proc sql;
create table out as select * from gg where name in(&ts.);
quit;
%mend;

%file1(&dd.)

proc print;
run;
[/pre]

Because your global macro variable dd has a comma,so macro variable ts will
resolve to have two parameters,then you need macro function %str() to quote it to avoid to resolve.


Ksharp
Cynthia_sas
SAS Super FREQ
Hi:
It's really not a good idea to "prequote" your macro variable values. Even though there are ways to use macro quoting functions to avoid some of the problems that you will encounter, you cannot anticipate all the ways you might need to protect your "prequoted" macro variable value.

Aside from all the other issues of how your comma in the list is being interpreted incorrectly, you might run into all sorts of quoting problems down the road.

So, let's work backward from the correct code. This is the correct PROC SQL that you want to generate:
[pre]
proc sql;
create table out as
select *
from gg
where name in("tt", "rr");
quit;
[/pre]

and you want to pass in the values tt and rr for the WHERE clause. So rather than prequoting the value of &DD and putting the comma into the parameter value, which as you discovered, does not work. It would be better to create the &DD macro variable like this:
[pre]
%let dd= tt rr;
[/pre]

...and then just use macro logic and macro processing to break apart &DD into separate strings and build a specific &INLIST macro variable to use in this way:
[pre]
proc sql;
create table out as
select *
from gg
where name in(&inlist);
quit;
[/pre]

You can easily break up &DD into pieces using a %DO %UNTIL loop and then rebuild your &INLIST macro variable using a simple %DO loop, as shown in the program below. This avoids all the pitfalls of prequoting macro variables. It is far better to create your string for the IN test under program control and add commas and quotes to the information from &DD based on logic that will work all the time.

cynthia
[pre]
** it is not a good practice to "prequote" macro variables;
** so pass in the values separated by a space;
%let dd= tt rr;

** read the data;
data gg;
input id name $2.;
datalines;
1 tt
2 rr
3 gh
;
run;

%macro file1(ts);
** parse out parameters separated by space;
%let i=1;
%let inlist=;

** Use DO UNTIL macro loop to parse;
** use the QUOTE function to add quotes around each parameter;
%do %until (%qscan(&ts,&i,%str( ))= %str());
%let word&i=%qscan(&ts,&i,%str( ));
%let word&i = %sysfunc(quote(&&word&i));
%let i=%eval(&i+1);
%end;

** get the number of strings in the parameter list;
%let numstr = %eval(&i -1);

** build the INLIST macro variable using a macro DO loop;
** and insert the comma appropriately between every value except the last;
** (when macro var &I = macro var &NUMSTR);
%do i = 1 %to &numstr;
%if &i lt &numstr %then %let inlist = &inlist.&&word&i., ;
%else %if &i eq &numstr %then %let inlist = &inlist.&&word&i;
%end;

** show what the value of the "working" macro variables are;
%put word1=&word1 word2=&word2 i=&i numstr=&numstr after processing;
%put inlist= &inlist;

** run the query;
** since the strings and commas are in the right place in the &INLIST;
** macro variable, you do not have to worry about quotes or commas;
** and can just reference &INLIST;
proc sql;
create table out as
select *
from gg
where name in(&inlist);
quit;

** show the results and the original value of the &DD macro variable;
ods listing;
proc print data=work.out;
title 'After PROC SQL';
title2 "looking for values in NAME field: &dd";
run;
%mend;

%file1(&dd.);

[/pre]
Ksharp
Super User
Hi. I think there are too many codes, One sql statement can do that.


[pre]
%let dd= 'tt' 'rr';
ods listing;
data gg;
input id name $2.;
datalines;
1 tt
2 rr
3 gh
;
data g_g;
set gg;
where name in (&dd);
run;
options mprint symbolgen;
proc sql;
select quote(name)
into :inlist separated by ' '
from g_g;

create table out as
select *
from gg
where name in(&inlist);
quit;
proc print;
run;
[/pre]



Ksharp
Cynthia_sas
SAS Super FREQ
I know, it seems like a lot of code. However, it is only a %DO %UNTIL and a %DO loop.

And I start from the premise that it's a bad idea to "prequote" macro variable values. My approach is just one approach, one possible approach out of many, that will bypass the need to "prequote" macro variable values. If you don't understand the possible problems with "prequoting", or you choose to ignore the possible problems with "prequoting", then don't use my suggestion.

I've been programming SAS macro code for a LONG time and over that time, the "no prequoting" guideline has proved valuable for me.

cynthia
Ksharp
Super User
Hi.Cynthia
I think you have the right idea.
and I totally agree with what you said.
You are old expert with sas programming,So forgive me,
I just want to write a solution. *^_^*
Cynthia_sas
SAS Super FREQ
Hi, Ksharp:

I understand. You are absolutely entitled to your opinion and your own approach for a solution! No need to ask for forgiveness.

I prefer to think of myself as a "well-seasoned" or "mature" sas programmer -- (as opposed to "old") -- even though the number for my birthdate in SAS internal storage format is a negative number (which means I was born before Jan 1, 1960).

Of course, I started programming in SAS when I was 10.... so I can still have over 25 years' experience and not be -that- old.....yet!

cynthia
Ksharp
Super User
Sorry. My english is not good, because english is not my mother langue.
So there are some problem when i express my emotion. and I just want to say you are seasoned expert of SAS.

Regards
K#
Cynthia_sas
SAS Super FREQ
It's OK. Really. Thanks for the compliment!
Patrick
Opal | Level 21
Hi Cynthia

I strongly agree with you that pre-quoting is not a good idea.

Only: In this special case here the values to be used could also contain blanks as valid characters, i.e. %let dd= t t, xx;
So what would you use in the scan() function as separater to cover for all possible cases (even a comma could be a valid character in the string)?

I therefore think that in this code - as an exception - one can accept pre-quoting.

Or... may be re-think the whole approach and find a solution which doesn't need macro coding at all.

Just an opinion.

Patrick
Cynthia_sas
SAS Super FREQ
Yes, Patrick,
...you're right, it is a narrow solution, based on the premise that the data was as illustrated (name is always 2 characters and the WHERE clause is always going to use a list of names to find) and the need for the "IN list" was the ONLY use for the macro variables. Since I don't totally understand the need for a macro program in this situation, I took the requirements to be very narrow in scope. If there were other requirements, I would have NOT used my approach at all, I would have designed it differently, I think.

And ... I would probably not have used a macro PROGRAM at all, if this were my task to do. In fact, I agree with you that I would have tried first for a solution that didn't involve macro coding at all. I suspect that there's a bigger picture behind the original question and one reason I can think of for wanting a macro variable for the IN list is that eventually, the list will probably come from some automated process or dataset, in which case, I think some fairly straightforward table lookup techniques, without SAS macro programming, would probably take care of the getting the right observations extracted. But, in the absence of a bigger picture or context for the question, I tried to point out what I considered to be the biggest drawback of the approach in the OP question.

cynthia
Paige
Quartz | Level 8
So let's take this idea of not pre-quoting macro variables a step further.

Suppose I want to set up a macro that does the quoting and creating of a string for later use, so I don't have to edit Cynthia's %file1 macro each time I want to change the SQL. In other words, I want to put a %MEND after Cynthia's

%do i = 1 %to &numstr;
%if &i lt &numstr %then %let inlist = &inlist.&&word&i., ;
%else %if &i eq &numstr %then %let inlist = &inlist.&&word&i;
%end;

This could result in a general macro that I can use prior to any PROC SQL where such a list is needed. My solution, so far, has been to make &INLIST a %GLOBAL variable inside this macro, and then in the PROC SQL outside of the macro, I insert the %GLOBAL variable &INLIST into the SQL WHERE clause. Is there a better way to handle this?
Cynthia_sas
SAS Super FREQ
I tend to shy away from "better" and only think of "different" solutions. If I had been designing this, I would either have made the whole macro program a LOT more generic (without a hardcoded file name, select and where, etc) or I would have probably used a table lookup technique and no macro at all or I would have not put the SQL step inside the macro program -- so I could have the choice of SQL or some other procedure that used &INLIST.

I try not to redesign macro programs that other people have posted for their questions, because frequently people simplify the questions/programs they post and I suspected, here, that we were not seeing the whole program flow.

I have my own "rules" or guidelines that I follow, which guide my design. I'm comfortable with multiple ampersand references and %DO loops -- one of my hard and fast rules is to avoid pre-quoting. For other folks, prequoting doesn't bother them and they would rather avoid multiple ampersand references.

I'm sure that other folks have some cool "utility" macros that they use to do things like build in lists or lists of variables. But, you have to benchmark these approaches in a real design with real data and real test conditions in order to determine whether the different design qualifies as "better" -- because you have to decide what is "better" -- better CPU time, better (easier) code to maintain, better I/O statistics, better clock time to execute. More verbose code might take a fraction more CPU time, but would be easier to maintain in the long run. Is that "better" -- it depends on what you are optimizing for.

cynthia
Tim_SAS
Barite | Level 11
> More
> verbose code might take a fraction more CPU time, but
> would be easier to maintain in the long run. Is that
> "better" -- it depends on what you are optimizing
> for.

“We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil. Yet we should not pass up our opportunities in that critical 3%.A good programmer will not be lulled into complacency by such reasoning, he will be wise to look carefully at the critical code; but only after that code has been identified” - Donald Knuth
Cynthia_sas
SAS Super FREQ
Great quote!

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 14 replies
  • 2652 views
  • 0 likes
  • 6 in conversation