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 "¶Directory./¶Filename..&outputFileExtensionExcel.";
%if ¶UseColumnLabels. = true %then %do;
data refFile.¶WorksheetName.
(DBLABEL = yes)
;
set ¶DataSetName.;
run;
%end;
%else %do;
data refFile.¶WorksheetName.;
set ¶DataSetName.;
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.
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 "¶Directory./¶Filename";
data refFile.¶WorksheetName.
%if %upcase(¶UseColumnLabels)= TRUE %then
%do;
(DBLABEL = yes)
%end;
;
set ¶DataSetName.;
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
);
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.
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:
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
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 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;
Hi i tried to follow these offical sas documentations
SAS Docu SAS9.4
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.