topic Re: How to resolve a macro variable with single quotes around in SAS Programming

How to resolve a macro variable with single quotes around

KiranMaddi — Mon, 22 Jun 2015 10:58:51 GMT

Hi all

I have a date value with out single quotes. I am hooking into sql and filtering the data using where statement. But the macro variable seems to be not working. I am sure that the probolem is the date does not have single quotes.
Is there a macro function that resolves the macro variable with quotes, here is my code below:

Thanks in advance!

%macro Loop;

%let today = %sysfunc(today(),yymmdd10.);

proc sql exec;
connect to odbc (&connectA);

SELECT *
FROM connection to odbc
(
SELECT count(*) as row_count FROM [xxxxx].[xxxx].[xxxxx] A WITH (NOLOCK)
WHERE date = "&today"

);
disconnect from odbc;
quit;
%mend

macro varible date resolves to 2015-06-22 but I want the date in single quotes('2015-06-22') How can I do that?

Re: How to resolve a macro variable with single quotes around

andreas_lds — Mon, 22 Jun 2015 11:30:17 GMT

Just add single quotes: WHERE date = "'&today'"

Re: How to resolve a macro variable with single quotes around

KiranMaddi — Mon, 22 Jun 2015 11:50:07 GMT

Hi Andreas

Tried your suggestion. seems not working.

ERROR: CLI describe error: [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name

''2015-06-16''. : [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not

be prepared.

Re: How to resolve a macro variable with single quotes around

Patrick — Mon, 22 Jun 2015 11:59:27 GMT

To single quote a string you would need something like: where date_string=%unquote(%str(%')&today%str(%'))

You're running this in-database and I would assume column "date" is actually of type date/datetime. If so then I would assume you don't have to pass-in your value as a character string but as something the data base can treat as a date value. How exactly to pass in the value depends on the database.

Assuming it's SQL Server it would be something like: where date=cast(%unquote(%str(%')&today%str(%')) as datetime)

....but then: Instead of using a SAS Date function and then convert it to a SQL Server date value in a pass-through block you could also directly use a SQL Server Date function Date Functions in SQL Server and MySQL

Re: How to resolve a macro variable with single quotes around

Ksharp — Mon, 22 Jun 2015 12:12:01 GMT

Not sure if it could work on Pass-through.

Code: Program

  %let today= %sysfunc(today(),yymmdd10.);
  %let fix=%unquote(%str(%'&today%'));
  options symbolgen;
proc sql;
select *
 from sashelp.class
   where name= &fix ;
   quit;

Log: Program

Notes (2)

 1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 SYMBOLGEN: Macro variable _SASWSTEMP_ resolves to /folders/myfolders/.images/d68a90fb-ff4b-4300-be59-7d1dc90f4a3b
 SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
 SYMBOLGEN: Macro variable GRAPHINIT resolves to  
 57  
 58 %let today= %sysfunc(today(),yymmdd10.);
 59 %let fix=%unquote(%str(%'&today%'));
 SYMBOLGEN: Macro variable TODAY resolves to 2015-06-22
 60 options symbolgen;
 61 proc sql;
 62 select *
 63 from sashelp.class
 64 where name= &fix ;
 SYMBOLGEN: Macro variable FIX resolves to '2015-06-22'
 NOTE: No rows were selected.
 65 quit;
 NOTE: PROCEDURE SQL used (Total process time):
  real time 0.11 seconds
  cpu time 0.10 seconds
   

Xia Keshan

Re: How to resolve a macro variable with single quotes around

KiranMaddi — Mon, 22 Jun 2015 12:12:26 GMT

Excellent stuff Patrick!!

Thanks a lot. Both are working but I am going with the first one.

Re: How to resolve a macro variable with single quotes around

KiranMaddi — Mon, 22 Jun 2015 12:14:43 GMT

Thanks buddy! But that is not working on pass through

Re: How to resolve a macro variable with single quotes around

Patrick — Mon, 22 Jun 2015 12:43:29 GMT

I faced actually not too long ago exactly the same problem and I had a lot of cases where I needed single quoting of macro variables. I ended up with implementing a function style macro (stored in a folder which is part of the Autocall facility).

Here the code and how it's used.

%macro util_quote_val(

in_string, /* source string to be quoted */

not_NULL_flg /* if empty source string: return two quotes instead of NULL */

);

%if %bquote(&in_string)=%bquote() %then

%do;

%if %bquote(&not_NULL_flg)=%bquote() %then

%do;

%str(NULL)

%end;

%else

%do;

%str('')

%end;

%else

%do;

%unquote(%str(%')%trim(%left(&in_string))%str(%'))

%end;

%mend;

%let name=Alfred;

proc sql;

select *

from sashelp.class

where name=%util_quote_val(&name,1);

;

quit;

....and if you've got your solution then can you please mark the answers as helpful and correct? Especially the correct "helps us" to concentrate on questions which still require a solution.

Re: How to resolve a macro variable with single quotes around

KiranMaddi — Mon, 22 Jun 2015 12:58:50 GMT

Hi Patrick,

I have actually clicked on your answer as correct as soon as the code worked for me and somehow it did not register my click.

Any ways thanks for you great help:-)

Re: How to resolve a macro variable with single quotes around

KiranMaddi — Mon, 22 Jun 2015 12:59:13 GMT

Thanks all for your help. Really appreciated :smileylaugh:

Re: How to resolve a macro variable with single quotes around

KiranMaddi — Thu, 25 Jun 2015 07:33:29 GMT

Hi Patrick sorry for asking a question on a closed discussion. How do I add parenthesis as well around a macro variable. something like this?

I want to see &today like this ('&today')

where date_string=%unquote(%str(%'()&today%str(%')))

Thanks in advance!

Re: How to resolve a macro variable with single quotes around

Patrick — Thu, 25 Jun 2015 08:14:55 GMT

Something like below should work:

%let today = %sysfunc(today(),yymmdd10.);

%put where date_string=(%unquote(%str(%')&today%str(%')));

Re: How to resolve a macro variable with single quotes around

Ksharp — Thu, 25 Jun 2015 12:35:24 GMT

where date_string=%unquote(%str(%'%()&today%str(%'%)))

Re: How to resolve a macro variable with single quotes around

Tom — Thu, 25 Jun 2015 14:53:35 GMT

Here is simple solution for adding single quotes. Not sure why you would want to remove the leading and trailing spaces that someone would actually have to work pretty hard to get passed into the macro.

%macro squote(value);

%unquote(%str(%')%qsysfunc(tranwrd(%superq(value),%str(%'),''))%str(%'))

%mend squote;

Re: How to resolve a macro variable with single quotes around

Patrick — Thu, 25 Jun 2015 21:34:43 GMT

"Not sure why you would want to remove the leading and trailing spaces"

The macro I've posted was from a real implementation and there it made sense to remove such blanks should they ever occur. Most of the logic in the macro is actually about replacing empty input strings with either two quotes or keyword NULL.