BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
t75wez1
Quartz | Level 8

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
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/

 

View solution in original post

11 REPLIES 11
Reeza
Super User
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
Super User

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
Quartz | Level 8

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

Reeza
Super User
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
Super User
Again, why not just run ODS EXCEL and proc print with labels to the excel file?

t75wez1
Quartz | Level 8

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

Thanks so much for your prompt helps.

 

Reeza
Super User

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
Quartz | Level 8

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
Super User
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
Quartz | Level 8

Thanks for your helps again!

 

SAS INNOVATE 2024

Innovate_SAS_Blue.png

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. 

Register now!

How to Concatenate Values

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 434 views
  • 3 likes
  • 4 in conversation