Macro variable containing single quotes

[MitchHolt]

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?

[jakarman]

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

[MitchHolt]

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

[jakarman]

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.        

[MitchHolt]

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

[Astounding]

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 <> '';

   ;

[MitchHolt]

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

[JackHamilton]

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'

[leelee]

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.