topic Re: Macro variable containing single quotes in SAS Programming

Macro variable containing single quotes

MitchHolt — Thu, 28 May 2015 18:59:38 GMT

I'm completely lost - i'm sure this question has been asked and answered before but i can't make sense of what I've read so far.

I have a macro that is building out a proc sql statement. I need the where statement to include matched single quotes.

I have this now:

WhereSt= "&WhereSt &FieldName <> '''' ";

And the result should be something like:

Where Agent <> ''

How on earth do I do that?

Re: Macro variable containing single quotes

jakarman — Thu, 28 May 2015 19:14:48 GMT

http://blogs.sas.com/content/sgf/2014/08/15/macro-quoting-made-easy/

Re: Macro variable containing single quotes

MitchHolt — Thu, 28 May 2015 19:21:26 GMT

Still not clear on what I'm supposed to put where - almost everything I've done still ends with an error. The next line of code is a Do statement and I get an error Macro Keyword Do appears as text

Re: Macro variable containing single quotes

jakarman — Thu, 28 May 2015 19:36:38 GMT

Quotes and quoting is one of the most terrible things to solve and work on. The simple reason is they are used for that many goals and meanings.
Generating those strings with a datastep or scl is less challenging than using macro-s. As you are saying the next statement is a DO statement (no %) you are most likely in a datastep.
Within that you only have the meaning of quotes for strings, but the complication is that using 2-quotes with no space in between is set back as a single one when used for the charstring.
Your SQL statement is having the ' ' (one space) for indicating a null-string or SAS missing. When the SQL is referring an external DBMS the NULL-values are mostly translated to ' ' but sometimes NULL is not the same as it is a 3 value logic not the true/false digital logic.

Re: Macro variable containing single quotes

Astounding — Thu, 28 May 2015 19:54:58 GMT

As Japp said, this can be complicated. And you may need to remove any blanks between your single quotes. But it doesn't need to get this complicated. It looks like you already have some macro variables to work with, and need to construct the SQL WHERE clause. I would expect to see something like this to add a condition about &FieldName to an existing WHERE clause:

proc sql;

create .....

where x > 1 %if %length(&FieldName) %then and &FieldName <> '';

;

Re: Macro variable containing single quotes

MitchHolt — Thu, 28 May 2015 20:01:50 GMT

So the next statement is %do - it's part of a macro. And the Where statement gets built up, this macro is iterated and builds up multiple fieldnames each iteration so I can't just build out the where statement with the variable directly

Re: Macro variable containing single quotes

MitchHolt — Thu, 28 May 2015 20:14:16 GMT

I changed the logic to be is not null which seems to be working - it'll take several hours for the code to fully run but I think that works and avoids all the worry about the quotes - thanks for the responses

Re: Macro variable containing single quotes

JackHamilton — Fri, 29 May 2015 14:55:53 GMT

I use %BQUOTE.

In the following example using pass-through SQL, Oracle expects a date string inside single quotes.

%let want_date = 2015-05-01;

proc sql;

connect to oracle as dorrdb

(path=dorrdb user=&NRUSER. password=&NRPASS.);

select * from connection to dorrdb

(

select count(*)

from vdw.vdw_rx

where rownum <= 10 and

rxdate = DATE %BQUOTE('&WANT_DATE.')

);

disconnect from dorrdb;

quit;

what Oracle sees is rxdate = DATE '2015-05-01'

Re: Macro variable containing single quotes

leelee — Fri, 29 May 2015 16:08:01 GMT

The paper SAS® Macro: Symbols of Frustration? %Let us help! A Guide to Debugging Macros

contains some good information about troubleshooting macros, including how to handle special characters.