The SAS Output Delivery System and reporting techniques

Export Multi Sheets to excel

Reply
Occasional Contributor
Posts: 8

Export Multi Sheets to excel

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.

Valued Guide
Posts: 2,177

Re: Export Multi Sheets to excel

This is one for the ods snd base reporting forum

SAS Super FREQ
Posts: 8,820

Re: Export Multi Sheets to excel

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;

Occasional Contributor
Posts: 8

Re: Export Multi Sheets to excel

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


Frequent Contributor
Posts: 89

Re: Export Multi Sheets to excel

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

how to do this with office >2010 ??

Herman

Occasional Contributor
Posts: 8

Re: Export Multi Sheets to excel

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;

Valued Guide
Posts: 3,208

Re: Export Multi Sheets to excel

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.

---->-- ja karman --<-----
Valued Guide
Posts: 2,177

Re: Export Multi Sheets to excel

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

Occasional Contributor
Posts: 8

Re: Export Multi Sheets to excel

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

Ask a Question
Discussion stats
  • 8 replies
  • 9223 views
  • 2 likes
  • 5 in conversation