topic Re: Creating SAS code to modify labels - apostrophe problem in SAS Programming

Creating SAS code to modify labels - apostrophe problem

pteranodon — Tue, 09 Sep 2014 19:53:25 GMT

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.

Re: Creating SAS code to modify labels - apostrophe problem

ballardw — Tue, 09 Sep 2014 20:06:48 GMT

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

Re: Creating SAS code to modify labels - apostrophe problem

Hima — Tue, 09 Sep 2014 20:06:57 GMT

Quote function does that. Below is an example.

data _null_;

x='label';

y=quote(x);

put y;

run;

Re: Creating SAS code to modify labels - apostrophe problem

Hima — Tue, 09 Sep 2014 20:13:51 GMT

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;

Re: Creating SAS code to modify labels - apostrophe problem

Tom — Tue, 09 Sep 2014 20:27:59 GMT

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";

Re: Creating SAS code to modify labels - apostrophe problem

pteranodon — Tue, 09 Sep 2014 20:45:21 GMT

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.

Re: Creating SAS code to modify labels - apostrophe problem

Tom — Tue, 09 Sep 2014 21:07:14 GMT

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.