Working with single quotes and double quotes

[jdmarshg]

I'm fairly new to using SAS DI and using base SAS code.

One thing when researching about coding practices but I still find to be quite inconsistent is when and where to use single quotes and double quotes.

For example one macro variable that I created (parameter) I use double quotes within an openquery.

The other macro variable sends back and error but still populates the database.

Can anyone send me some links and or documentation to resolve this in my mind of best practice?

Here is the query below:

(SELECT column FROM OPENQUERY(MYSQL, 'select datetime from ''&thisdoesnotwork'' where datasetname="&thisresolvesandworks" and Status = "Done" order by rowid

desc limit 1'));

I receive an error from the first macro variable embedded......

This query gets passed through sqlserver to mysql.

[Tom]

Macro expressions are NOT evaluated inside of single quotes.  For that you need to use double quotes.  Note that if you have complex strings with quotes nested inside of other quotes it is the outer quotes that determine whether SAS will evaluate the macro expression.

Try these examples:

%let x=Value ;

%put '&x';

%put "&x";

%put 'The value of x is "&x".' ;

%put "The value of x is '&x'." ;

Another rule is that to include the quote character itself in the string you need to double it.

%put "The value of x is ""&x""." ;

Also watch out for the rules imposed by database system that you are passing the query to.  SAS does not care whether you quote strings with single or double quotes, but some database systems do care.  For example Teradata uses single quotes for character literals and uses double quotes for names.

[art297]

: While I agree with what you said, I really have to question whether the filename in the from clause should have been quoted at all.

[Tom]

Some databases allow you to use quotes around object names.  Similar to SAS's name literals, but without the n suffix.

select *

from "my database"."my table"

order by "my variable"

[art297]

I'm not sufficiently familiar with Oracle SQL to know whether object names can or can't be quoted but, since they don't have to be quoted, I would suggest that the OP try removing them to see if that corrects the problem.

[RichardinOz]

In general and within SAS itself either single or double quotes can be used interchangeably, provided that they are used in matching pairs.  Windows users may be more used to double quotes while users of other OS' or databases may prefer single quotes.  Your choice.

If quotes are located within quotes then either the internal quotes must be doubled up

     'this is a dog''s breakfast'  (2 single quotes between g and s)

or alternating quotes can be used

     "this is a dog's breakfast".

The main exception to interchangeability within SAS is that macro expressions (not just macro variables) inside single quotations are not evaluated while those in double quotes are, as has been covered by Tom.  Note that double quotes inside single quotes do not allow macro expressions to be evaluated.

Where SAS has to interact with external software, eg the operating system or a relational database then the rules of the eternal application must be followed; double quotes for Windows and single quotes in most other cases.  This can be inconvenient when the user wants to pass values via a macro variable.  The answer is to 'mask' the single quote so that it is hidden from SAS but passed to the application.  One way of masking a single quote is to 'escape' it (SAS unhelpfully calls this macro quoting) thus:   %STR(%')

The complete expression may need to be "unquoted" (in SAS terms) for the character to be recognised externally.  So what the OP may have been trying to achieve is possibly

     ... %UNQUOTE(%STR(%')select datetime from &thisshouldwork where datasetname=&thisshouldalsowork and ... %STR(%')) ;

You can define a macro function that will manage the masking:

     %Macro Wrap (contents) ;

          %* No semicolon on the next line! ;

          %UNQUOTE(%STR(%')&contents%STR(%'))

     %Mend ;

So the SQL above would become

     %Wrap (select datetime from &thisshouldwork where datasetname=&thisshouldalsowork and ... ) ;

Richard temporarily back in OZ