Add format that quotes using single quotes

[Tom]

The format $QUOTE will enclose the value in double quote characters and properly handles doubling up any embedded double quote characters..  It would be useful to have a format that would generated a quoted string using single quote characters.  String literals quoted using single quotes will suppress any embedded macro triggers and can be used in pass-through SQL code.

Perhaps they could add a letter to the end of the format name like they have done with the date formats.

[PaigeMiller]

Depending on what you are doing, it would be very simple to surround text with single quotes. I have done it many times. Can you give us an example of what you are trying to do?

[Tom]

@PaigeMiller

There are ways to convert using QUOTE() function, but not with a format.

Say for example I had a table of code/decode values and I needed to generate an SQL CASE statement like this where double quotes indicate an object name and single quotes indicate a string literal.

case when ("oldvar" = 'A') then 'Group A' end as "newvar"

from data like:

VARNAME|OLDVALUE|NEWVALUE|TARGETVAR
oldvar|A|Group A|newvar

If there was a $QUOTES format then you could write the code using PUT statements like this:

if first.varname then put 'case ';
put 'when (' varname :$quote. '=' oldvalue :$quotes. ') then ' value :$quotes. ;
if last.varname then put 'end as ' targetvar :$quote.;

[kmw]

I support formats in Technical Support at SAS and will consider requesting a new format for this purpose.  If it's approved it would be for a future release so in the  meantime, here's a solution that uses PROC FCMP to write a function and then PROC FORMAT uses the function as a format.

proc fcmp outlib=work.myfncs.stuff;

function singquote(x $) $200;

return(quote(trim(x),"'"));

endsub;

options cmplib=work.myfncs;

proc format;

value $singquote(min=3 max=200 default=32) other=[singquote()];

run;

data _null_;

length x $200;

x='hello';

put x=singquote100.;

x="don't worry it's OK";

put x=singquote100.;

x='has "double" quotes';

put x=singquote100.;

run;

/* written to log */

x='hello'

x='don''t worry it''s OK'

x='has "double" quotes'