resolve macro variable single quotes in SQL

fre — Wed, 04 May 2016 09:27:29 GMT

Hi,

Have searched for a solution for my problem, but can't find it.

I'm using an SQL in a macro, and I want to resolve a macrovariable that needs to be between two single quotes in the WHERE clause:

WHERE t2.CENTRUM = '&centrum'

when resolved, it should look like this:

WHERE t2.CENTRUM = 'centre1'

Whatever I'm trying, I cant resolve &centrum when it's between the single quotes. Double quotes will resolve it, but I need the single quotes, otherwise my sql will not function.

What am I missing here?

Full code:

%macro create_list_per_centre;

%do c = 1 %to 5;

%if &c = 1 %then %let centrum = centre1;
%else %if &c = 2 %then %let centrum = centre2;
%else %if &c = 3 %then %let centrum = centre3;
%else %if &c = 4 %then %let centrum = centre4;
%else %if &c = 5 %then %let centrum = centre5;

PROC SQL;

   CREATE TABLE WORK.&centrum AS
   SELECT t2.CENTRUM,
          t1.KEY,
          (put(t2.YR,4.) || 'm' || strip(put(t2.periode2,2.))) AS PERIODE,

      FROM WORK.error_ALL t1
           LEFT JOIN tableYYY t2 ON (t1.KEY = t2.KEY)
      WHERE t2.CENTRUM = '&centrum'
      ORDER BY t2.CENTRUM,
               t1.KEY,
               t1.error_type;
QUIT;

%end;

%mend;

Re: resolve macro variable single quotes in SQL

Ksharp — Wed, 04 May 2016 09:43:58 GMT

Here is an example :

%let sex=F;

proc sql;
select *
 from sashelp.class
  where sex=%unquote(%bquote('&sex'));
quit;

Re: resolve macro variable single quotes in SQL

fre — Wed, 04 May 2016 09:54:52 GMT

Thank you very much, it worked.

Re: resolve macro variable single quotes in SQL

BrunoMueller — Wed, 04 May 2016 10:23:14 GMT

Please also have a look here https://communities.sas.com/docs/DOC-11204

There is a autocall macro %TSLIT that can be used and also an alternative macro provided by @Tom

Bruno

topic resolve macro variable single quotes in SQL in SAS Programming

resolve macro variable single quotes in SQL

Re: resolve macro variable single quotes in SQL

Re: resolve macro variable single quotes in SQL

Re: resolve macro variable single quotes in SQL