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!
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.