Hi all,
Before exporting the SAS data set to Excel file, I tried to rename their variable names by using variable's labels.
Some of variable labels have spaces between the words.
How to automatically rename variable names using their labels with filling a underscore (e.g. '_') if there is a space between the words of the label?
Need help with SAS coding.
Thank you.
data class;
set sashelp.class;
label name="NAme" sex="GEndeR" age="Age in years" height="HeiGht in inches" weight="weight in lbs";
run;
ods excel file='/home/fkhurshed/demo.xlsx' options(sheet_name = "example name");
proc print data=class label noobs;
run;
ods excel close;
ODS EXCEL usage
https://blogs.sas.com/content/sgf/2017/02/20/tips-for-using-the-ods-excel-destination/
Why?
Variable labels can be over 200 characters long, variable names are limited to 32 characters. Are you really trying to stuff 10 pounds of stuff into a 5 pound bag?
My variable labels are sort of dictionary of variable names even filling with a underscore between the words.
data class;
set sashelp.class;
label name="NAme" sex="GEndeR" age="Age in years" height="HeiGht in inches" weight="weight in lbs";
run;
proc sql noprint;
select catx('=', name, transtrn(lowcase(trim(label)), " ", "_"))
into :rename_list separated by " " from sashelp.vcolumn where libname='WORK'
and upper(memname)='CLASS';
quit;
%put &rename_list;
proc datasets lib=work nodetails nolist;
modify class;
rename &rename_list;
run;
quit;
proc print data=class;
run;
I'll definitely give a try. Do you have any example to share?
Thanks so much for your prompt helps.
data class;
set sashelp.class;
label name="NAme" sex="GEndeR" age="Age in years" height="HeiGht in inches" weight="weight in lbs";
run;
ods excel file='/home/fkhurshed/demo.xlsx';
proc print data=class label noobs;
run;
ods excel close;
@t75wez1 wrote:
I'll definitely give a try. Do you have any example to share?
Thanks so much for your prompt helps.
Thanks for your ODS solution.
If exporting it to Excel, i have a control to give a tab name(see below). How to give a customized tab name in ODS Excel?
PROC EXPORT DATA= tmp_view OUTFILE= "&outputname." DBMS=xlsx REPLACE;
SHEET="&tabname.";
data class;
set sashelp.class;
label name="NAme" sex="GEndeR" age="Age in years" height="HeiGht in inches" weight="weight in lbs";
run;
ods excel file='/home/fkhurshed/demo.xlsx' options(sheet_name = "example name");
proc print data=class label noobs;
run;
ods excel close;
ODS EXCEL usage
https://blogs.sas.com/content/sgf/2017/02/20/tips-for-using-the-ods-excel-destination/
Thanks for your helps again!
Catch the best of SAS Innovate 2025 — anytime, anywhere. Stream powerful keynotes, real-world demos, and game-changing insights from the world’s leading data and AI minds.
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.
Ready to level-up your skills? Choose your own adventure.