BookmarkSubscribeRSS Feed
jdmarshg
Obsidian | Level 7

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 rowiddesc limit 1'));

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

This query gets passed through sqlserver to mysql.

5 REPLIES 5
Tom
Super User Tom
Super User

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
Opal | Level 21

: 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
Super User Tom
Super User

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
Opal | Level 21

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
Quartz | Level 8

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


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