Set variable to macro variable with ampersand

Question

Not sure how to title this as the title is still pretty ambiguous but what I'm doing is.

PROC SQL NOPRINT;
    SELECT LABEL INTO :head
    FROM dictionary.columns
    WHERE UPCASE(MEMNAME)='PROCSQLDATA' AND UPCASE(NAME)=%UPCASE("&var.");
QUIT;

DATA want;
    SET have;
    head="%SUPERQ(&head.)";
RUN;

So what I'm doing with the code is setting a macro variable "head" to the label of the variable "&var." within the data set "procsqldata". So let's say the label for one of the variables that I'm throwing into the proc sql is Adam&Steve. How do I set that to a variable within a data set without throwing an error. One way that I tried to cheat, which doesn't work because I may be doing it wrong is doing

 %LET steve='&steve';

but that doesn't seem to work and it just does an infinite loop on the data step for some reason.

Answer 1

A few points.

First the %SUPERQ() function wants the NAME of the macro variable to quote. So if you write:

%superq(&head)

the macro processor will evaluate the macro variable HEAD and use the value as the name of the macro variable whose value you want it to quote. Instead write that as:

%superq(head)

Second macro triggers are not evaluated inside of strings that use single quotes on the outside. So this statement:

%let steve='&steve';

will set the macro variable Steve to single quote, ampersand, s, t, .... single quote.

But note that if you macro quote the single quotes then they do not have that property of hiding text from the macro processor. So something like:

%str(%')%superq(head)%str(%') 

or

%bquote('%superq(head)')

Will generate the value of the macro variable HEAD surrounded with quotes.

So you might get away with:

head = %bquote('%superq(head)') ;

Although sometimes that macro quoting can confuse the SAS compiler (especially inside of a macro) so now that you have the single quotes protecting the ampersand you might need to remove the macro quoting.

head = %unquote(%bquote('%superq(head)')) ;

But the real solution is not to use macro quoting it at all.

Either pull the value using the SYMGET() function.

head = symget('head');

(make sure to set a length for the dataset variable HEAD or SAS will default it to $200 because of the function call).

Or better still just leave the label in a variable to begin with instead of trying to confuse yourself (and everyone else) by stuffing it into a macro variable just so you can then pull it back into a real variable.

PROC SQL NOPRINT;
create table label as SELECT LABEL 
    FROM dictionary.columns
    WHERE LIBNAME='MYLIB' and MEMNAME='PROCSQLDATA' and UPCASE(NAME)=%UPCASE("&var.")
;
QUIT;

DATA want;
  SET have;
  if _n_=1 then set label;
  head = label;
  drop label;
run;

Answer 2

%SUPERQ takes the name of the argument, retrieves the value of it in a macro quoted context.

You might have better long term understanding if the symbol name used (head) is a name with more contextual meaning (such as var_label). Also, your DICIONARY.COLUMNS query should include a criteria for libname=. Note: LIBNAME and MEMNAME values are always uppercase in DICTIONARY.COLUMNS. Name, which is the column name, can be mixed case and needs the upcase to compare for name equality.

PROC SQL NOPRINT;
    SELECT LABEL INTO :var_label
    FROM dictionary.columns
    WHERE 
       LIBNAME = 'WORK' and
       MEMNAME = 'PROCSQLDATA' and
       UPCASE(NAME)=%UPCASE("&var.")
    ;
QUIT;

data labels;
  set have;
  head_label = "%superq(var_label)";
run;

An & in SUPERQ argument means the name of the macro variable, whose value is to be retrieved, is found as the value of a different macro variable.

 %let p = %nrstr(Some value & That%'s that);
 %let q = p;
 %let v = %superq(&q);
 %put &=v;
 -------- LOG -------
 V=Some value & That's that

&q become p and %superq retrieved value of p for assignment to v

Note: In some situations, you can retrieved the label of a variable in a running data step using the VLABEL or VLABELX functions.

 data have;
   label weight = 'Weight (kg)';
   retain weight .;

   weight_label_way1 = vlabel ( weight );
   weight_label_way2 = vlabelx('weight');
 run;

Answer 3

There will be a quoting function that can solve this, but I can never remember what they all do, and I find it best to avoid them, for my own sanity and that of my coworkers.

In this case, you don't need to resolve the macro variable into a string literal (ie head = "&head";) at all; you can use SYMGET:

DATA want;
  SET have;
  head = SYMGET('head');
RUN;

See the docs for the SYMGET function here:

https://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n00cqfgax81a11n1oww7hwno4aae.htm&docsetVersion=9.4&locale=en

On an unrelated note, you should also read the 'DICTIONARY Tables and Performance' section at the end of this page:

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=sqlproc&docsetTarget=n02s19q65mw08gn140bwfdh7spx7.htm&locale=en

You might be surprised how much faster your first query will run if you removed the UPCASE functions from the WHERE clause.