DATA Step, Macro, Functions and more

resolve macro variable single quotes in SQL

Accepted Solution Solved
Reply
Contributor fre
Contributor
Posts: 35
Accepted Solution

resolve macro variable single quotes in SQL

[ Edited ]

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;


Accepted Solutions
Solution
‎05-04-2016 05:54 AM
Super User
Posts: 10,048

Re: resolve macro variable single quotes in SQL

Here is an example :

 

%let sex=F;

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

View solution in original post


All Replies
Solution
‎05-04-2016 05:54 AM
Super User
Posts: 10,048

Re: resolve macro variable single quotes in SQL

Here is an example :

 

%let sex=F;

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

Re: resolve macro variable single quotes in SQL

Thank you very much, it worked.

SAS Super FREQ
Posts: 709

Re: resolve macro variable single quotes in SQL

Hi

 

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 439 views
  • 1 like
  • 3 in conversation