topic Re: Export Multi Sheets to excel in ODS and Base Reporting

Export Multi Sheets to excel

SASNewUser — Thu, 24 Jul 2014 21:15:33 GMT

I am trying to export multi files to separate sheets within a single excel workbook. I am SAS 9.3 SAS EG 5.1.

proc export data=test

dbms=excelcs

outfile c:\temp

sheet='jul 24';

quit;

I want to add each day to the monthly spreadsheet.

Re: Export Multi Sheets to excel

Peter_C — Thu, 24 Jul 2014 21:31:14 GMT

This is one for the ods snd base reporting forum

Re: Export Multi Sheets to excel

Cynthia_sas — Sat, 26 Jul 2014 02:51:34 GMT

Hi:

First of all, ODS will not "add" to an existing workbook, so you can't do what you want with ODS, as an alternative to PROC EXPORT. ODS either creates a CSV, HTML or XML file completely new every time you run your job. So you could not add each day to a monthly workbook using ODS unless you started on day 1 and created a workbook with a sheet for day1; then on day 2, you created a workbook with 2 sheets for day 1 and day 2; then on day 3, you recreated the entire workbook with 3 sheets for day 1, day 2 and day 3; etc, etc.

Using PROC EXPORT, you can created a new sheet in an existing workbook every day, your syntax is incorrect. you need a fully qualified workbook name in outfile= (you're also missing the = sign). See the attached code and imaging that every step for a different age was being run on a different day. Note how the outfile= stays the same in each step, but the sheet= option changes. And, the input data selected rows changes for every day, too.

Cynthia

proc export data=sashelp.class(where=(age=11))

outfile="c:\temp\multi.xls"

dbms=excelcs replace label;

sheet="Monday";

run;

proc export data=sashelp.class(where=(age=12))

outfile="c:\temp\multi.xls"

dbms=excelcs replace label;

sheet="Tuesday";

run;

proc export data=sashelp.class(where=(age=13))

outfile="c:\temp\multi.xls"

dbms=excelcs replace label;

sheet="Wednesday";

run;

Re: Export Multi Sheets to excel

jakarman — Sat, 26 Jul 2014 09:23:21 GMT

Why not use the libname access method. Every sheet is seen as a table.

http://www2.sas.com/proceedings/sugi31/024-31.pdf The reason of seeing a table twice is explained. Ranges within Excel.

Re: Export Multi Sheets to excel

Peter_C — Tue, 29 Jul 2014 15:04:21 GMT

if the libname engine seems unavailable, there is an option for ODS generated woorkbooks (note this is in a new workbook because ods creates a new file)

ODS TAGSETS.EXCELXP file='path/your.work.book.xml' ;

ODS TAGSETS.EXCELXP options(SHEET_INTERVAL='bygroup'

SHEET_LABEL='trial' ) ;

Proc print or report or tabulate

...... more statements

By by_variable;

run;

See papers like

http://support.sas.com/resources/papers/proceedings12/150-2012.pdf

Re: Export Multi Sheets to excel

SASNewUser — Thu, 21 Aug 2014 11:32:38 GMT

Thank you this helped now all I have to do is figure out how to format it the way people want to see it without having to do it manually

Re: Export Multi Sheets to excel

SASNewUser — Thu, 21 Aug 2014 11:34:29 GMT

I have been reading up on tagsets.msoffice2k_x would this also work with formating the excel spreadsheet

Re: Export Multi Sheets to excel

Jaheuk — Thu, 21 Aug 2014 11:55:59 GMT

when using dbms=xlsx, the result only has ONE sheet?

how to do this with office >2010 ??

Herman

Re: Export Multi Sheets to excel

SASNewUser — Thu, 21 Aug 2014 14:25:50 GMT

libname test pcfiles path=\\{your info here}\TEST.XLSX; (this has to be a different name else it is an error)

proc export data=sashelp.cars outfile='\\{your info}\test.xlsx'

dbms=xlsx; sheet=cars; (first dbms=xlsx log will tell you not created ..... overwritted .....)

run;

proc export data=sashelp.class outfile='\\{your info}\test7_delete.xlsx'

dbms=excelcs; sheet=class ; (all other dbms=EXCELCS and it seems to work very well)?????

run;

proc export data=sashelp.classfit outfile='\\{your info}\test7_delete.xlsx'

dbms=excelcs; sheet=classfit ;

run;

proc export data=sashelp.deskact outfile='\\{your info}\test7_delete.xlsx'

dbms=excelcs;

sheet=deskact ;

run;

libname test clear;