BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
KiranMaddi
Obsidian | Level 7

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

14 REPLIES 14
andreas_lds
Jade | Level 19

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

KiranMaddi
Obsidian | Level 7

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.

Patrick
Opal | Level 21

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

KiranMaddi
Obsidian | Level 7

Excellent stuff Patrick!!

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

Patrick
Opal | Level 21

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.

KiranMaddi
Obsidian | Level 7

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

Tom
Super User Tom
Super User

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;

Patrick
Opal | Level 21

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

KiranMaddi
Obsidian | Level 7

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!

Patrick
Opal | Level 21

Something like below should work:

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

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

Ksharp
Super User

OR

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

Ksharp
Super User

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

KiranMaddi
Obsidian | Level 7

Thanks buddy! But that is not working on pass through

KiranMaddi
Obsidian | Level 7

Thanks all for your help. Really appreciated :smileylaugh:

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 30010 views
  • 5 likes
  • 5 in conversation