DATA Step, Macro, Functions and more

How to resolve a macro variable with single quotes around

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 107
Accepted Solution

How to resolve a macro variable with single quotes around

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?


Accepted Solutions
Solution
‎06-22-2015 07:59 AM
Respected Advisor
Posts: 3,892

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

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

View solution in original post


All Replies
Super Contributor
Posts: 259

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

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

Frequent Contributor
Posts: 107

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

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.

Solution
‎06-22-2015 07:59 AM
Respected Advisor
Posts: 3,892

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

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

Frequent Contributor
Posts: 107

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

Excellent stuff Patrick!!

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

Respected Advisor
Posts: 3,892

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

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;

    %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.

Frequent Contributor
Posts: 107

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

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:-)

Super User
Super User
Posts: 6,500

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

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;

Respected Advisor
Posts: 3,892

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

"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.

Frequent Contributor
Posts: 107

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

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!

Respected Advisor
Posts: 3,892

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

Something like below should work:

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

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

Super User
Posts: 9,681

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

OR

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

Super User
Posts: 9,681

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

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

Frequent Contributor
Posts: 107

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

Thanks buddy! But that is not working on pass through

Frequent Contributor
Posts: 107

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

Thanks all for your help. Really appreciated :smileylaugh:

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 14 replies
  • 4591 views
  • 2 likes
  • 5 in conversation