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?
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
Just add single quotes: WHERE date = "'&today'"
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.
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
Excellent stuff Patrick!!
Thanks a lot. Both are working but I am going with the first one.
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(¬_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.
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:-)
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;
"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.
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!
Something like below should work:
%let today = %sysfunc(today(),yymmdd10.);
%put where date_string=(%unquote(%str(%')&today%str(%')));
OR
where date_string=%unquote(%str(%'%()&today%str(%'%)))
Not sure if it could work on Pass-through.
%let today= %sysfunc(today(),yymmdd10.);
%let fix=%unquote(%str(%'&today%'));
options symbolgen;
proc sql;
select *
from sashelp.class
where name= &fix ;
quit;
Notes (2)
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
Thanks buddy! But that is not working on pass through
Thanks all for your help. Really appreciated :smileylaugh:
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.