A Comparison of SAS Functions Designed for Creating Excel Output in a Stand-alone Environment and a BI Environment. Koichi Satoh Takumi Information Technology Co., Ltd. ODS EXCELXP ODS HTML ODS CSVALL DDE LIBNAME EXCEL EXPORT ODS EXCELXP ODS HTML ODS CSVALL DDE LIBNAME EXCEL EXPORT 2 1
3 4 2
ODS TAGSETS.EXCELXP ODS HTML ODS CSVALL DDE LIBNAME EXCEL EXPORT 5 ODS EXCELXP ODS TAGSETS.EXCELXP FILE = "filename" ; ODS TAGSETS.EXCELXP CLOSE ; Sample ODS TAGSETS.EXCELXP FILE = EXCELXP.XLS ; PROC PRINT DATA = SASHELP.CLASS ; RUN ; ODS TAGSETS.EXCELXP CLOSE ; ODS 6 3
ODS HTML ODS HTML FILE = "filename" ; ODS HTML CLOSE ; Sample ODS HTML FILE = HTML.XLS ; PROC PRINT DATA = SASHELP.CLASS ; RUN ; ODS HTML CLOSE ; ODS HTML SAS Ver 9.1 ODS 7 ODS CSVALL ODS CSVALL FILE = "filename" ; ODS CSVALL CLOSE ; Sample ODS CSVALL FILE = CSV.XLS ; PROC PRINT DATA = SASHELP.CLASS ; RUN ; ODS CSVALL CLOSE ; ODS CSVALL ODS TAGSETS.CSVALL CSVALL CSV 8 4
DDE Dynamic Data Exchange) FILENAME fileref DDE 'DDE-triplet' <DDE-options>; Sample FILENAME OUT DDE excel sheet1!r1c1:r100c100 NOTAB ; DATA _NULL_ ; FILE OUT ; SET SASHELP.CLASS ; PUT (_ALL_)( '09'x ) ; RUN ; FILENAME CMD DDE excel system ; DATA _NULL_ ; FILE CMD ; PUT [QUIT()] ; RUN ; 9 LIBNAME EXCEL LIBNAME libref <engine> <'SAS-data-library'> ; Sample LIBNAME IN EXCEL "LIBNAME.xls" ; DATA IN.EXCEL ; SET SASHELP.CLASS ; RUN; SAS sheet1 n 10 5
EXPORT PROC EXPORT DATA=<libref.>SAS-data-set <(SAS-data-set-options)> OUTFILE="filename" OUTTABLE="tablename" <DBMS=identifier> <REPLACE>; <data-source-statement(s);> RUN; Sample PROC EXPORT DATA = SASHELP.CLASS OUTFILE = " EXPORT.xls" DBMS = EXCEL ; SHEET = sample ; RUN ; 11 12 6
13 SAS/ACCESS Interface to PC Files 14 7
15 16 8
17 18 9
19 20 10
ODS TAGSETS.EXCELXP ODS HTML ODS CSVALL DDE LIBNAME EXCEL EXPORT 21 ODS EXCELXP ODS HTML ODS CSVALL DDE LIBNAME EXCEL EXPORT 22 11
ODS TAGSETS.EXCELXP ODS HTML ODS CSVALL DDE LIBNAME EXCEL EXPORT 23 ODS EXCELXP ODS HTML ODS CSVALL DDE LIBNAME EXCEL EXPORT DDE SAS/ACCESS Interface to PC Files SAS/ACCESS Interface to PC Files 24 12
25 BISP AMO BISP AMO 26 13
XLS Base SAS SAS/ACCESS Interface to PC Files 27 ODS TAGSETS.EXCELXP ODS TAGSETS.EXCELXP 28 14
ODS TAGSETS.EXCELXP http://support.sas.com/rnd/base/ods/odsmarkup/excltags.tpl SASUSER %include 'c: temp excltags.tpl'; libname xxx 'c: temp' ; ods path(prepend) xxx.templat(update); %include 'c: temp excltags.tpl'; libname xxx 'c: temp' ; ods path(prepend) xxx.templat(read); 29 ODS TAGSETS.EXCELXP OPTION WEB http://support.sas.com/rnd/base/ods/odsmarkup/excelxp_help.html SAS ODS TAGSETS.EXCELXP file='c: temp test.xml' OPTIONS ( Doc = 'Help' ); ODS TAGSETS.EXCELXP CLOSE; 30 15
ODS TAGSETS.EXCELXP OPTIONS PAPERSIZE = A4; Orientation Scale FitToPage Pages_FitWidth Pages_FitHeight OPTIONS PAPERSIZE = A4; ODS TAGSETS.EXCELXP FILE = c: temp sample1.xls STYLE = minimal OPTIONS (Orientation = 'Portrait Scale = 100' ) ; ODS TAGSETS.EXCELXP CLOSE ; ODS TAGSETS.EXCELXP OPTIONS (Orientation = 'landscape FitToPage = 'yes' Pages_FitWidth = '1' Pages_FitHeight = '100' ) ; 31 SAS Institute Inc. ODS MARKUP Resources. http://support.sas.com/rnd/base/ods/odsmarkup/ SAS Institute Japan. SAS 1 ODS. 32 16