topic Re: How to automatically rename variable names using their labels? in SAS Programming

How to automatically rename variable names using their labels?

t75wez1 — Mon, 12 Jun 2023 20:21:52 GMT

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.

Re: How to automatically rename variable names using their labels?

Reeza — Mon, 12 Jun 2023 20:33:25 GMT

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.

Re: How to automatically rename variable names using their labels?

ballardw — Mon, 12 Jun 2023 20:34:14 GMT

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?

Re: How to automatically rename variable names using their labels?

t75wez1 — Mon, 12 Jun 2023 20:40:12 GMT

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

Re: How to automatically rename variable names using their labels?

Reeza — Mon, 12 Jun 2023 20:56:34 GMT

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;

Re: How to automatically rename variable names using their labels?

Reeza — Mon, 12 Jun 2023 20:57:03 GMT

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

Re: How to automatically rename variable names using their labels?

t75wez1 — Mon, 12 Jun 2023 21:03:44 GMT

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

Thanks so much for your prompt helps.

Re: How to automatically rename variable names using their labels?

Kurt_Bremser — Mon, 12 Jun 2023 21:08:54 GMT

PROC PRINT Statement

Re: How to automatically rename variable names using their labels?

Reeza — Mon, 12 Jun 2023 21:10:41 GMT


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.

Re: How to automatically rename variable names using their labels?

t75wez1 — Mon, 12 Jun 2023 21:59:06 GMT

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

Re: How to automatically rename variable names using their labels?

Reeza — Mon, 12 Jun 2023 22:11:16 GMT

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/

Re: How to automatically rename variable names using their labels?

t75wez1 — Fri, 16 Jun 2023 22:05:01 GMT

Thanks for your helps again!