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.
This is one for the ods snd base reporting forum
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;
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
when using dbms=xlsx, the result only has ONE sheet?
how to do this with office >2010 ??
Herman
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;
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.
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
I have been reading up on tagsets.msoffice2k_x would this also work with formating the excel spreadsheet
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.