DATA Step, Macro, Functions and more

Creating SAS code to modify labels - apostrophe problem

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Creating SAS code to modify labels - apostrophe problem

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.


Accepted Solutions
Solution
‎09-09-2014 04:27 PM
Super User
Super User
Posts: 7,076

Re: Creating SAS code to modify labels - apostrophe problem

Posted in reply to pteranodon

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

View solution in original post


All Replies
Super User
Posts: 11,343

Re: Creating SAS code to modify labels - apostrophe problem

Posted in reply to pteranodon

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

Regular Contributor
Posts: 233

Re: Creating SAS code to modify labels - apostrophe problem

Posted in reply to pteranodon

Quote function does that. Below is an example.

data _null_;

x='label';

y=quote(x);

put y;

run;

Regular Contributor
Posts: 233

Re: Creating SAS code to modify labels - apostrophe problem

Posted in reply to pteranodon

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;

Capture.JPG

Solution
‎09-09-2014 04:27 PM
Super User
Super User
Posts: 7,076

Re: Creating SAS code to modify labels - apostrophe problem

Posted in reply to pteranodon

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

New Contributor
Posts: 4

Re: Creating SAS code to modify labels - apostrophe problem

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.

Super User
Super User
Posts: 7,076

Re: Creating SAS code to modify labels - apostrophe problem

Posted in reply to pteranodon

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 742 views
  • 6 likes
  • 4 in conversation