BookmarkSubscribeRSS Feed
Yjn000
Fluorite | Level 6

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.

7 REPLIES 7
Patrick
Opal | Level 21

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;

Patrick_0-1678360158910.png

 

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
);

 

Yjn000
Fluorite | Level 6

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.

Kurt_Bremser
Super User

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:

Bildschirmfoto 2020-04-07 um 08.32.59.jpg

Yjn000
Fluorite | Level 6

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         
Kurt_Bremser
Super User

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.

Patrick
Opal | Level 21

@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;

Patrick_0-1678363145564.png

 

Yjn000
Fluorite | Level 6

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

SAS Docu Viya 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

Discussion stats
  • 7 replies
  • 723 views
  • 2 likes
  • 3 in conversation