DATA Step, Macro, Functions and more

how do I single quote a variable?

Reply
N/A
Posts: 0

how do I single quote a variable?

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
N/A
Posts: 0

Re: how do I single quote a variable?

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
Super User
Posts: 5,257

Re: how do I single quote a variable?

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
Data never sleeps
Respected Advisor
Posts: 3,894

Re: how do I single quote a variable?

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
N/A
Posts: 0

Re: how do I single quote a variable?

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

Re: how do I single quote a variable?

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.


Ask a Question
Discussion stats
  • 5 replies
  • 1202 views
  • 0 likes
  • 4 in conversation