Creating SAS code to modify labels - apostrophe problem

[pteranodon]

I'm trying to write code to modify the existing labels of a dataset by adding a suffix. This code is working for me:

%let ds=DS;
%let Suffix=MySuffix;
proc sql;
CREATE TABLE newlabels AS
SELECT "LABEL "||trim(name) || "='" || trim(label) || "&Suffix';" as labelcode
FROM dictionary.columns WHERE libname='WORK' and memname="&ds"
ORDER BY varnum;
quit;
data _null_;
file "H:\sas\Relabel.txt" notitle;
set newlabels end=lastobs;
if _n_= 1 then do;
  put "DATA &ds;";
  put "  SET &ds;";
  end;
put labelcode;
if lastobs=1 then do;
  put "run;";
  end;
run;
%include "H:\sas\Relabel.txt";

, until I reach a label that contains an apostrophe. That creates a big unmatched quote mess. Is there a function that I can place around 'trim(label)' in line 5 that will double any internal apostrophes?

Thanks.

[ballardw]

Nesting quotes you might want to try a single double quote within single quotes when building the string. Also use of the concatenation operators such as CATS might work a bit better:

cats("Label",name,'="',label,"&suffix",'";') as label code

[Hima]

Quote function does that. Below is an example.

data _null_;

x='label';

y=quote(x);

put y;

run;

[Hima]

In proc sql

data have;
input x $5.;
cards;
label
;
run;

proc sql;
create table want as
select x, (quote(x))||'=' as y from have;
quit;

proc print; run;

[Tom · Accepted solution]

Use the QUOTE() function. Or better still just use the $QUOTE. format.

%let ds=ds;

%let suffix=mysuffix;

proc sql noprint;

create table newlables as

  select varnum

       , name

       , catx(' ',label,"&suffix") as newlabel

    from dictionary.columns

    where libname='WORK' and memname=%upcase("&ds")

    order by varnum

;

quit;

data _null_;

  file 'h:\sas\relabel.txt' notitle;

  if _n_= 1 then put

    /  "data &ds;"

    /  "  set &ds;"

  ;

  if lastobs then put 'run;' ;

  set newlabels end=lastobs;

  put 'label ' name '=' newlabel :$quote. ';' ;

run;

%include "h:\sas\relabel.txt";

[pteranodon]

Thanks everyone. This gives me a lot to work with. Tom, how do get your syntax highlighted in these posts? I see syntax highlighting in the Advanced editor, but SAS is not an option in the list.

[Tom]

I keep a empty WORD file open.  Copy from SAS editor (ctrl-C) , Move to the WORD file paste over the old stuff from last time (ctrl-A ctrl-V).  Highlight and copy again (ctrl-A ctrl-C) and then move to the Forum editor and paste (ctrl-V).  The forum editor still has a nasty habit of eating the leading spaces of lines though.