BookmarkSubscribeRSS Feed
MitchHolt
Calcite | Level 5

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?


8 REPLIES 8
MitchHolt
Calcite | Level 5

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
Barite | Level 11

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.        

---->-- ja karman --<-----
MitchHolt
Calcite | Level 5

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
PROC Star

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
Calcite | Level 5

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
Lapis Lazuli | Level 10

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
SAS Employee

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.

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
  • 8 replies
  • 2231 views
  • 0 likes
  • 5 in conversation