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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.