05-28-2015 02:59 PM
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?
05-28-2015 03:21 PM
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
05-28-2015 03:36 PM
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.
05-28-2015 04:01 PM
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
05-28-2015 03:54 PM
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:
where x > 1 %if %length(&FieldName) %then and &FieldName <> '';
05-28-2015 04:14 PM
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
05-29-2015 10:55 AM
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;
connect to oracle as dorrdb
(path=dorrdb user=&NRUSER. password=&NRPASS.);
select * from connection to dorrdb
where rownum <= 10 and
rxdate = DATE %BQUOTE('&WANT_DATE.')
disconnect from dorrdb;
what Oracle sees is rxdate = DATE '2015-05-01'
05-29-2015 12:08 PM
contains some good information about troubleshooting macros, including how to handle special characters.