How do i handle ampersand in macro loop?

[WorkingMan]

Hi everyone. I am running a script that has macro to loop SQL statement. The thing is, it is possible for Macro variable to have value of Ampersand that is supposed to be treated as text. I tried various functions like superq and brquote but i dont understand any of the sample online and i will never get it working. It is always error for me. Can anyone help me on this?

Here is my code:

data subset;

Input Name $;
Alice!
Andy&Ace
P&L
A&L
BBB
CCC
ZZZ
XXX
;
run;





data master;
Input Name $;
Alice!
Andy&Ace
P&L
A&L
BBB
CCC
ZZZ
XXX
OOO
PPP
EEE
RRR
TTT
;
run;

proc sql noprint;

select Name into :name1-:name&SysMaxLong

from subset;

select count(*) into:cnt

from subset;

quit;



%macro loop();

proc sql;

create table final as

select * from master

where name IN ("&name1"

%do  i=1 %to &cnt;

,"&&name&i.."

%end;

);

quit;



%mend;

%loop;

[s_lassen]

You can use the QUOTE function to quote the values in the SQL expression. And if you use a single quote (second argument), SAS will not try to interpret the ampersands:

proc sql noprint;
  select quote(trim(Name),"'") into :name1-:name&SysMaxLong
  from subset;
  %let cnt=&sqlobs;
quit;

%macro loop();
proc sql;
  create table final as
  select * from master
  where name IN (&name1
  %do  i=2 %to &cnt;
    ,&&name&i
    %end;
);
quit;
%mend;

%loop;

I also used the SQLOBS Automatic macro variable to get the number of variables generated.

But it is much simpler to use a single macro variable and SEPARATED BY:

proc sql noprint;
  select quote(trim(Name),"'") into :names separated by ','
  from subset;
quit;

proc sql;
  create table final as
  select * from master
  where name IN (&names);
quit;

[WorkingMan]

Thanks for the clear message and code. Much appreciated. However, i notice that my data step:

data have;

name=a&w;

run;

It will throw error. I also tried wrapping the whole value with %nrstr and %quote, but it will give warning and create 2 variables called "a" and "w". How should i handle it?

[Kurt_Bremser]

---

@WorkingMan wrote:

Thanks for the clear message and code. Much appreciated. However, i notice that my data step:

data have;

name=a&w;

run;

It will throw error. I also tried wrapping the whole value with %nrstr and %quote, but it will give warning and create 2 variables called "a" and "w". How should i handle it?

---

Do you actually want to set name to the string"a&w"? If yes, do this:

data have;
name = 'a&w';
run;

The single quotes will prevent any attempt to resolve macro variables.

In your code, after the reslution of macro variable &w fails, the resulting code is equivalent to this:

data have;
name = a & w;
run;

which is equivalent to

data have;
name = a and w;
run;

Since the two missing values in (undefined) a and w are considered as false, the result of the right side is also a false, and name will be defined as numeric and set to zero.

[RW9]

Whilst I am sure that you will get an answer to your question, I would suggest that anything you do get, or try, will result in very messy, hard to maintain code.  Macro is not a data manipulation language, its one and only purpose is to generate text.  What that text is then used for is up to the next process - generally it gets feed into the Base SAS compiler to be operated upon.

Look at what it is you are trying to do, isolate the input data, and the required output, then assess a procedure or set of steps to get from one to the other.  Just from looking at the code you  provided (which really doesn't give a good picture), I would start by dropping the SQL thinking, look at proc means/summary - which are specifically built to get counts and other simple statistics from data, and use those to calculate the result.  This will be &cnt. + 1 proc step getting counts % faster, use &cnt. + 1 less resource, and be far simpler to maintain and expand upon.

If your data isn't in a good shape to do a simple proc over, then consider re-modelling your data to be easy to procedure over.  

As I have no test data to work with, I obviously can't show you an example with your data, but consider:

id1  var1 var2...

I I want totals of those, I could loop over that dataset, once for each column and do a sum() by id, or I could proc over that data once and do something like;

proc means data=have;
  by id;
  var var:;
  output out=want sum=;
run;

Effectively the same output, however it is one step (1 * read/write), rather than number of columns * read/write.  Also it will expand and shrink with your data without any need to modification or increase in resource.