BookmarkSubscribeRSS Feed
xxformat_com
Barite | Level 11

Hi,

I was wondering on whether there is any option which can be used with the xlsx engine to keep variable labels in export files as does the label option of proc export.

I know that dblabel can be used with the excel engine but what about the xlsx engine?

 


data class;
    set sashelp.class;
    label name='First Name';
run;

libname test xlsx "&xxtest/test_libname.xlsx";

data test.class;
    set class;
run;

libname test;

proc export data    = class
            dbms    = xlsx
            outfile = "&xxtest./test_procexport.xlsx"
            replace
            label;
run;
7 REPLIES 7
PaigeMiller
Diamond | Level 26

It looks like you have the code to try it. Why don't you try it and see what happens?

--
Paige Miller
xxformat_com
Barite | Level 11

I don't know any option which work with the xlsx engine using the libname statement.

My question is on whether someone knows an option I would not aware of.

PaigeMiller
Diamond | Level 26

What happens when you execute the code you show. Do the labels appear, or not?

--
Paige Miller
xxformat_com
Barite | Level 11
only proc export uses the label, not the other methods. But I'm not sure why you're asking it as I've already specify it in my question.
Reeza
Super User

Not sure what you're trying to do overall, but ODS EXCEL can split a file into two using BY as sheet_interval and then proc print with a label. 

 

proc sort data=sashelp.class out=class;
by sex;
run;

ods excel file='/home/fkhurshed/test.xlsx' options(sheet_interval = 'bygroup') style=minimal;
options nobyline;
proc print data=class noobs;
by sex;
run;

proc print data=class label noobs;
label age = 'Age (years)' weight = 'Weight (lbs)' height = 'Height (in)';
run;

ods excel close;

 

 

Tom
Super User Tom
Super User

Why not just try it?

602
603  libname x xlsx "C:\downloads\test_labels.xlsx" ;
NOTE: Libref X was successfully assigned as follows:
      Engine:        XLSX
      Physical Name: C:\downloads\test_labels.xlsx
604  data x.datastep(dblabel=yes); set class ; run;
                     -------
                     22

ERROR 22-7: Invalid option name DBLABEL.

NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


605  libname x clear;
NOTE: Libref X has been deassigned.

Looks like the answer is NO.

 

So use PROC EXPORT.

Or ODS EXCEL;

ods excel file= "C:\downloads\test_labels.xlsx" ;
proc print data=class label;
run;
ods excel close;
xxformat_com
Barite | Level 11
I was wondering on whether other options than the one I mentioned in my question could exist but I would not be aware of. db... options are for excel not for xlsx.

sas-innovate-white.png

Missed SAS Innovate in Orlando?

Catch the best of SAS Innovate 2025 — anytime, anywhere. Stream powerful keynotes, real-world demos, and game-changing insights from the world’s leading data and AI minds.

 

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 1731 views
  • 0 likes
  • 4 in conversation