how do I single quote a variable?

[deleted_user]

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

[deleted_user]

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

[LinusH]

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

[Patrick]

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

[deleted_user]

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

[tomg_highmark]

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.