BookmarkSubscribeRSS Feed
SASNewUser
Calcite | Level 5

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.

8 REPLIES 8
Peter_C
Rhodochrosite | Level 12

This is one for the ods snd base reporting forum

Cynthia_sas
SAS Super FREQ

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;

SASNewUser
Calcite | Level 5

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


Jaheuk
Obsidian | Level 7

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

how to do this with office >2010 ??

Herman

SASNewUser
Calcite | Level 5

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;

jakarman
Barite | Level 11

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 --<-----
Peter_C
Rhodochrosite | Level 12

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

SASNewUser
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 19829 views
  • 2 likes
  • 5 in conversation