topic Re: SAS Columnlabels in Excel via library in SAS Programming

SAS Columnlabels in Excel via library

Yjn000 — Thu, 09 Mar 2023 10:33:05 GMT

Is it possible to output the column labels of a dataset via xlsx Libname? I can use the label output via proc export and ods excel. But i seem to miss something with the libname statement...

%let useColumnlabels = false;

%macro ExportDatasetInFileExcel(
        paraDirectory /*string*/
        , paraFilename /*string*/
        , paraWorksheetName /*string*/
        , paraDataSetName /*string*/
        , paraUseColumnLabels = &useColumnlabels. /*string true false */
    );

    libname refFile xlsx "&paraDirectory./&paraFilename..&outputFileExtensionExcel.";

    %if &paraUseColumnLabels. = true %then %do;
        data refFile.&paraWorksheetName.
                (DBLABEL = yes)
            ;
            set &paraDataSetName.;
        run;
    %end;
    %else %do;
        data refFile.&paraWorksheetName.;
            set &paraDataSetName.;
        run;
    %end;

    libname refFile clear;
%mend ExportDatasetInFileExcel;


data Example_DataSet_Labels;
  set sashelp.class;
  label name="Very  very very very very very long name of a very very very long string";
run;

%ExportDatasetInFileExcel(
    /home/sasserver/mypath
    , Filename
    , Workbook
    , Example_DataSet_Labels
    , paraUseColumnLabels = true
);

I tried the code above but got the following error

22: LINE and COLUMN cannot be determined.

NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.

ERROR 22-7: Invalid option name DBLABEL.

NOTE: The SAS System stopped processing this step because of errors.

Re: SAS Columnlabels in Excel via library

Patrick — Thu, 09 Mar 2023 11:25:35 GMT

Like always first make your SAS code fully work before you wrap a macro around it.

libname refFile xlsx "c:\temp\test.xlsx";
data refFile.Example_DataSet_Labels(DBLABEL = yes);
  set sashelp.class;
  label sex="Use Gender instead of Sex";
run;
libname refFile clear;

Once you know the SAS code works you can make it dynamic because now you know that any error you get is because of the macro "stuff" you're adding.

Below works for me.

%macro ExportDatasetInFileExcel(
        paraDirectory /*string*/
        , paraFilename /*string*/
        , paraWorksheetName /*string*/
        , paraDataSetName /*string*/
        , paraUseColumnLabels = false /*string true false */
    );

    libname refFile xlsx "&paraDirectory./&paraFilename";

    data refFile.&paraWorksheetName.
      %if %upcase(&paraUseColumnLabels)= TRUE %then
        %do;
          (DBLABEL = yes)
        %end;
        ;
      set &paraDataSetName.;
    run;

    libname refFile clear;
%mend ExportDatasetInFileExcel;


data Example_DataSet_Labels;
  set sashelp.class;
  label name="Very  very very very very very long name of a very very very long string";
run;

%ExportDatasetInFileExcel(
    c:\temp
    , test.xlsx
    , my_sheet
    , Example_DataSet_Labels
    , paraUseColumnLabels = true
);

Re: SAS Columnlabels in Excel via library

Kurt_Bremser — Thu, 09 Mar 2023 11:53:59 GMT

There is no global DBLABEL= dataset option, see here.

The DBLABEL= dataset option you want to use can only be used with a LIBNAME for the PC Files Server or relational databases.

Re: SAS Columnlabels in Excel via library

Patrick — Thu, 09 Mar 2023 12:02:33 GMT

@Kurt_Bremser wrote:

There is no global DBLABEL= dataset option, see here.

The DBLABEL= dataset option you want to use can only be used with a LIBNAME for the PC Files Server or relational databases.

@Kurt_Bremser The option is not global as you state but the SAS docu still calls it a dataset option: DBLABEL= Data Set Option

options obs=1;
libname refFile xlsx "c:\temp\test.xlsx";
data refFile.Sheet_Using_Label(DBLABEL = yes);
  set sashelp.class;
  label sex="Use Gender instead of Sex";
run;
data refFile.Sheet_Using_Varname(DBLABEL = no);
  set sashelp.class;
  label sex="Use Gender instead of Sex";
run;

proc print data=refFile.Sheet_Using_Label;
run;
proc print data=refFile.Sheet_Using_Varname;
run;
libname refFile clear;
options obs=max;

Re: SAS Columnlabels in Excel via library

Yjn000 — Fri, 10 Mar 2023 10:33:43 GMT

Hi Patrick,

thanks for your response.

Both your code examples with and without macro throw the same error as mine example.
Furthermore I reviewed your code and couldn't find any important difference to mine.

Re: SAS Columnlabels in Excel via library

Yjn000 — Fri, 10 Mar 2023 10:44:40 GMT

Hi i tried to follow these offical sas documentations
SAS Docu SAS9.4

SAS Docu Viya

Re: SAS Columnlabels in Excel via library

Kurt_Bremser — Fri, 10 Mar 2023 10:52:08 GMT

Please post the complete (all code and messages) of the non-macro code that resulted in the error.

Copy/paste the log text into a window opened with this button:

Re: SAS Columnlabels in Excel via library

Yjn000 — Fri, 10 Mar 2023 11:48:55 GMT

Just to clarify here is the code i am having troubles with

libname refFile xlsx "/home/Playground/myFile_Label.xlsx";

data refFile.Example_DataSet_Labels (dblabel = yes);
	set sashelp.class;
	label name="Very  very very very very very long name of a very very very long string";
run;

libname refFile clear;

and here is the generated log

1                                                          The SAS System                         Friday, March 10, 2023 08:46:00 AM

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='z_02_CodeInDevelopment_Snippets';
4          %LET _CLIENTPROCESSFLOWNAME='Playground';
5          %LET _CLIENTPROJECTPATH='Z:\Playground\Example.egp';
6          %LET _CLIENTPROJECTPATHHOST='MYSERVER';
7          %LET _CLIENTPROJECTNAME='Example.egp';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=PNG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         %macro HTML5AccessibleGraphSupported;
15             %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;
16         %mend;
17         FILENAME EGXLSSX TEMP;
18         ODS EXCEL(ID=EGXLSSX) FILE=EGXLSSX AUTHOR="********" STYLE=Excel
19         OPTIONS (
20          EMBEDDED_TITLES="yes" EMBEDDED_FOOTNOTES="yes"
21         );
22         
23         libname refFile xlsx "/home/Playground/myFile_Label.xlsx";
NOTE: Libref REFFILE was successfully assigned as follows: 
      Engine:        XLSX 
      Physical Name: /home/Playground/myFile_Label.xlsx
24         
25         data refFile.Example_DataSet_Labels (dblabel = yes);
                                                _______
                                                22
ERROR 22-7: Invalid option name DBLABEL.

26         	set sashelp.class;
27         	label name="Very  very very very very very long name of a very very very long string";
28         run;

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
      

29         
30         libname refFile clear;
NOTE: Libref REFFILE has been deassigned.
31         
32         %LET _CLIENTTASKLABEL=;
33         %LET _CLIENTPROCESSFLOWNAME=;
34         %LET _CLIENTPROJECTPATH=;
35         %LET _CLIENTPROJECTPATHHOST=;
36         %LET _CLIENTPROJECTNAME=;
37         %LET _SASPROGRAMFILE=;
38         %LET _SASPROGRAMFILEHOST=;
39         
40         ;*';*";*/;quit;run;
41         ODS _ALL_ CLOSE;
NOTE: Writing EXCEL(EGXLSSX) file: /opt/sas/ltv/work/SAS_workBBD800003E04_MYSERVER/#LN11670
42         
2                                                          The SAS System                         Friday, March 10, 2023 08:46:00 AM

43         
44         QUIT; RUN;
45