How to automatically rename variable names using their labels?

[t75wez1]

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.

[Reeza]

Do you really want underscores in Excel? you can have spaces in the name if the only purpose is to export, however you're still limited to 32 characters.

Note that depending on how you export to Excel you can also just have the labels show instead of the variable names.

[ballardw]

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?

[t75wez1]

My variable labels are sort of dictionary of variable names even filling with a underscore between the words.

[Reeza]

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;

[Reeza]

Again, why not just run ODS EXCEL and proc print with labels to the excel file?

[t75wez1]

I'll definitely give a try.  Do you have any example to share? 

Thanks so much for your prompt helps.

[Kurt_Bremser]

PROC PRINT Statement 

[Reeza]

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.

---

[t75wez1]

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

[Reeza · Accepted solution]

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/

[t75wez1]

Thanks for your helps again!