Tag: Quote

Quote macrovariables e.g. for an IN SQL-statement

The code below lets you qoute the contant of a macrovariables e.g. to be used in an IN SQL-statement.

%let Variables = age height weight;
%let InStatement = %Str(%’)%sysfunc(Tranwrd(&Variables.,%Str( ),%Str(%’, %’)))%Str(%’);

The macrovariable InStatement will contain ‘age’,’height’,’weight’. And can then be used in an IN SQL-statement.

Inserting quoted string in where sentence

There are different possibilities when you need to quote a string in SAS.

Before you read any further! Instead of using ‘ ‘ around the macro-variable use “ “.

The SQL-statement below will not work.

proc sql noprint;
insert into ToLib.DestDataset
select *
from FromLib.SourceDataset
where Year = ‘&Year.’;
quit;

The SQL-statement below will work.

proc sql noprint;
insert into ToLib.DestDataset
select *
from FromLib.SourceDataset
where Year = “&Year.”;
quit;

If you have a macro variable containing a year that could be 2012, and you need to put ‘ ‘ or “ “ around it, you can use the macro-functions %bquote as shown below.

My program works like this. I retrieve a year variable into a macro-variable from a configuration dataset.

proc sql noprint;
select upcase(Value) into :Year
from Config.Config_data
where upcase(Name) = upcase(‘Year’);
quit;

I remove blanks (if any)

%let Year = %trim(&Year.);

When I tried to quote the macro-variabel Year (it’s a string not a numeric) to get Year = ‘2012’ I got and error

proc sql noprint;
insert into ToLib.DestDataset
select *
from FromLib.SourceDataset
where Year = %bquote(‘&Year.’);
quit;

I found out that I needed to trim the %bquote-statement again. When I did that it worked.

proc sql noprint;
insert into ToLib.DestDataset
select *
from FromLib.SourceDataset
where Year = %trim(%bquote(‘&Year.’));
quit;

Another way to solve this would be to use

proc sql noprint;
select upcase(Value) format=$quote40. into :Year
from Config.Config_data
where upcase(Name) = upcase(‘Year’);
quit;

This will put “” around the year like “2012”.