topic Re: how do I single quote a variable? in SAS Programming

how do I single quote a variable?

deleted_user — Tue, 23 Sep 2008 20:55:18 GMT

Howdy.
I'm bringing in a SAS date value that I must convert to standard date format for use in a Teradata SQL statement. I need to put single quotes around this date.
How can I do this?
my ending SQL statement would be something like:
Select * From some_table where mydate = '2008-09-08'

Example code:

%macro mcvDateFormat;
%let MCV_END_DATE='23Sept08'd
%if %length(&MCV_END_DATE) > 1 %then
%do;
%if %length(&MCV_END_DATE)=9 %then
%do;
%let mcv_request_end=%sysfunc(inputn(&MCV_END_DATE,date9.0));
%put We are using date 9;
%end;
%else %if %length(&MCV_END_DATE)=10 %then
%do;
%let mcv_request_end=%sysfunc(inputn(&MCV_END_DATE,mmddyy10.));
%put We are using mmddyy 10 &mcv_request_end;
%end;
%else
%do;
%let mcv_request_end=%sysfunc(putn(&MCV_END_DATE, is8601da.));
%put We have a SAS date so we convert to iso date &mcv_request_end;
%end;

%end;

/* try to quote date here */
%let test ='&mcv_request_end';
%put &test;

%mend;

%mcvDateFormat;

Thank you
Patrick

Re: how do I single quote a variable?

deleted_user — Tue, 23 Sep 2008 22:06:49 GMT

Patrick,

There are multiple ways how one can accomplish this. When using macros you can use
%str function that removes meaning from special characters (not all of them!). I suggest you read up on the function.

here is the example:

%let mcv_request_end=23SEP08;

%let mcv_request_end=%str(%'&mcv_request_end%');
%put &mcv_request_end;

ikp

Re: how do I single quote a variable?

LinusH — Wed, 24 Sep 2008 06:43:49 GMT

Are you using SQL pass-thru? If not, I believe that you could use a SAS date literal, which SAS/ACCESS engine will convert automatically to a Teradata data value:

Select * From some_table where mydate = &MVC_END_DATE;

If using pass-thru, I think the easiest way is to create your macro variable in a data step:

%let MCV_END_DATE='23Sep08'd;

data _null_;
call symput('MCV_REQUEST_END',"'"||put(&MCV_END_DATE,yymmdd10.)||"'");
run;

Regards,
Linus

Re: how do I single quote a variable?

Patrick — Wed, 24 Sep 2008 11:10:25 GMT

Hi Patrick

%put ***********************************************;
%let MCV_END_DATE='23Sep08'd;
%put That is not a SAS date: &MCV_END_DATE;
%put The SAS macro compiler deals with text only;

%put ***********************************************;
%let MCV_END_DATE=%sysfunc(inputn('23Sep08',date9.));
%put THAT is text with digits representing the days since 1/1/1960: &MCV_END_DATE;

%put ***********************************************;
%let MCV_END_DATE=%str(%')%sysfunc(inputn('23Sep08',date9.),is8601da.)%str(%');
%put That is may be what you are looking for: &MCV_END_DATE;

HTH
Patrick

Re: how do I single quote a variable?

deleted_user — Wed, 24 Sep 2008 12:55:07 GMT

Thanks so much everyone! And you are correct Linus, I'm doing a SQL Pass-thru

Re: how do I single quote a variable?

tomg_highmark — Thu, 29 Nov 2012 18:54:46 GMT

when supplying macro variables to teradata that need to be surrounded by single quotes, i use the %bquote(&macrovar) if the macro variable has the quote marks included as LinusH does with the symput statement.

the other use of %bquote('&macrovar') is to supply the single quotes inside the parentheses with the macrovar name.