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.
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";
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
Quote function does that. Below is an example.
data _null_;
x='label';
y=quote(x);
put y;
run;
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;
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";
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.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.