topic Re: xlsx engine: can variable label be exported in SAS Programming

xlsx engine: can variable label be exported

xxformat_com — Sat, 24 Jun 2023 11:28:15 GMT

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;

Re: xlsx engine: can variable label be exported

PaigeMiller — Sat, 24 Jun 2023 13:48:45 GMT

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

Re: xlsx engine: can variable label be exported

xxformat_com — Mon, 26 Jun 2023 16:55:08 GMT

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.

Re: xlsx engine: can variable label be exported

Reeza — Mon, 26 Jun 2023 17:31:05 GMT

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;

Re: xlsx engine: can variable label be exported

PaigeMiller — Mon, 26 Jun 2023 17:21:34 GMT

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

Re: xlsx engine: can variable label be exported

Tom — Mon, 26 Jun 2023 17:50:59 GMT

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;

Re: xlsx engine: can variable label be exported

xxformat_com — Mon, 26 Jun 2023 21:03:02 GMT

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.

Re: xlsx engine: can variable label be exported

xxformat_com — Mon, 26 Jun 2023 21:05:23 GMT

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.