Proc Export to Excel - Multiple tables to one xlsx file on different sheets

[SasGuy614]

I'm trying to figure out a way to export multiple data sets into the same Excel file but on different sheets. For example, I would like to export these three tables to 'April14DataSheet.xlsx'

Data set 1 = Sashelp.Class

Data set 2 = Sashelp.Classfit

Data set 3 = Sashelp.Shoes

I know how to export one table into a sheet but I don't know how to add additional tables to the newly created excel file from the export.

/*Excel Export*/

PROC EXPORT DATA= Sashelp.Class.  /*Sheet 1*/

        outfile= "/opt/apps/User1/April14DataSet..xlsx "

        dbms=xlsx replace;

        sheet="Class";

run;

Thanks!

[slchen]

PROC EXPORT DATA= Sashelp.Class outfile= "c:\temp\want.xlsx " dbms=xlsx replace;
sheet="Class";
PROC EXPORT DATA=sashelp.cars outfile="c:\temp\want.xlsx" dbms=xlsx;
sheet="Car";
run;

[SasGuy614]

I think i'm getting close. When I run the code the first proc export runs successfully and then I get an error message on the second part. The errorr: Output file already exist. Specify REPLACE option to overwrite it. Any suggestions?

PROC EXPORT DATA= Sashelp.Class

  outfile= "/opt/apps/User1/April14DataSet..xlsx "

  dbms=xlsx replace;

  sheet="CLASS";

PROC EXPORT DATA=sashelp.cars

  outfile= "/opt/apps/User1/April14DataSet..xlsx "

  dbms=xlsx;

  sheet="CAR";

run;

[Reeza]

I'm not sure XLSX actually supports multiple sheets.

Can you use Tagsets.ExcelXP or do you need a native SAS file?

If the datasets are small you may also want to consider XLS files.

[Reeza]

libname test pcfiles path="C:\_localdata\test_delete.xlsx";

data test.class;

    set sashelp.class;

run;

data test.cars;

    set sashelp.cars;

run;

libname test;

This works as in no error in the log file, but Excel won't open it, say's its corrupt.

This may work, it depends on your set up:

libname test excelcs "C:\_localdata\test_delete.xlsx";

data test.class;

    set sashelp.class;

run;

data test.cars;

    set sashelp.cars;

run;

libname test;

[happy_bunny]

libname and multiple data steps work for me after quit; and closing the session

without these I get the 'corrupt' excel sheet

I use both 9.3 and 9.4, and have outputted both .xls and .xlsx files

[RW9]

And have you tried clearing the libname e.g. libname test clear;

Basically, whilst the libname is in effect there is a link to the Excel file.  Until that link is closed then the file is not completely written to (i.e. corrupted).  Hence it works when SAS is closed as all libraries are closed.

IMO though, you are far better off with the excelxp tagset, though bear in mind there are limitations in Excel such as how many rows and columns, and you also have to bear in mind, will anyone actually use an Excel file of 65k records?  I certainly wouldn't.  If I want data then XML, CSV, dataset is far better, for review there are better alternatives, patient profiles, summarised data etc.

[Reeza]

51580 - The XLSX engine is enhanced to write multiple sheets per Microsoft Excel file in the first m...

Hotfix....

[PeteD]

Earlier versions used to do this without a problem.   These latest releases seems have a lot of instances like this where the new version breaks the old.  And SAS calls the Hotfix an enhancement?  What happened to the vaunted quality???

[ChrisHemedinger]

Hi @PeteD, you're commenting on a thread that originated a couple of years ago.  Are you experiencing a new problem?  If so, post a new topic with specifics (if you want community members to check the work and verify) or contact SAS Technical Support (if you want to track for a resolution).

Chris

[slchen]

Try to delete April14DataSet..xlsx in your filefold by hand, then run the code again. The codes work for me.

8    PROC EXPORT DATA= Sashelp.Class outfile= "c:\temp\want.xlsx " dbms=xlsx replace;
9    sheet="Class";
10

NOTE: The export data set has 19 observations and 5 variables.
NOTE: "c:\temp\want.xlsx" file was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
      real time           0.02 seconds
      cpu time            0.00 seconds


11   proc export data=sashelp.cars outfile="c:\temp\want.xlsx" dbms=xlsx;
12   sheet="Car";
13
14   run;

NOTE: The export data set has 428 observations and 15 variables.
NOTE: "c:\temp\want.xlsx" file was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
      real time           0.06 seconds
      cpu time            0.04 seconds

[SasGuy614]

I worked with the SAS Admin team and they have installed the hotfix. I have tried with with writing the files to an xls file and it seems to work fine. However, when I change it to the xlsx extension I still can't successfully write a file. I often deal with records > 65k rows so the xlsx extension is ideal. The second log shown below seems to show what the issue is.

Any help would be appreciated.

PROC EXPORT DATA= Sashelp.Class

  outfile= "Server Path/Data/Sample.xlsx"

  dbms=xlsx replace;

  sheet="CLASS";

PROC EXPORT DATA=sashelp.Class

  outfile= "Server Path/Data/Sample.xlsx"

  dbms=xlsx;

  sheet="CAR";

run;

NOTE: The export data set has 19 observations and 5 variables.

NOTE: "/Server Path/Data/Sample.xlsx" file was successfully created.

NOTE: PROCEDURE EXPORT used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

NOTE: Export cancelled.  Output file /Server Path/Data/Sample.xlsx already

      exists. Specify REPLACE option to overwrite it.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE EXPORT used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

[RW9]

Hi,

Not sure what your intent for the output is here.  You specify that you regularly deal with > 65k rows so the xlsx extension is ideal?  If you want to view the data in Excel , which is why you would use either XLS/X file format, then you are always going to hit that threshold as that is hardwired into Excel.  If you mean that you want a flat file, XML based, which can hold any amount of data, e.g for transport, then you are best off writing XML or for size considerations CSV.

So the question is, what are you using the output for?

A final word, if you want to export to Excel, then can I suggest you check out the ExcelXP tagset, as its simpler syntax and has far more options than the export to xlsx.

[slchen]

I use SAS9.4. It did not work even you have installed Hotfix, you could use  Tagsets.ExcelXP as jiwillis suggested.

[jwillis · Accepted solution]

SASGuy,

It sounds like Reeza's solution is best for you.  My solution was to use ODS Tagsets.ExcelXP.  The only problem is that you have to manually save the .xml file as an .xlsx file.

.

filename myfile "H:\SASREPORTS\JIM THOMPSON\ALL REPORTS IN ONE WORKBOOK.XML";
ods tagsets.excelxp body=myfile STYLE=HTMLBLUE
    OPTIONS(SHEET_NAME="LOCATION_AND_BRAND" Orientation='Landscape' wraptext = 'YES' sheet_interval='Proc'
    autofilter='ALL' FROZEN_HEADERS='YES' convert_percentages='YES' TITLE_FOOTNOTE_WIDTH='20' EMBEDDED_TITLES='YES'
    EMBEDDED_FOOTNOTES='YES' pagebreaks='YES' gridlines='YES' PAGE_ORDER_ACROSS='YES' contents='NO' gridlines='YES');

/*** one ***/
TITLE "REPORT ONE FOR JIM THOMPSON. PRODUCT AND LOCATION, FOR DATES &wk_begin. THROUGH &wk_start.";
proc report data=gspe.finalJTrpt&rpttype.

/*** two ***/
ods tagsets.excelxp OPTIONS(SHEET_NAME="PROD_LOC_PARAMETER");
TITLE "REPORT TWO FOR JIM THOMPSON. PRODUCT, LOCATION AND PARAMETER, FOR DATES &wk_begin. THROUGH &wk_start.";
proc report data=gspe.combined2&rpttype.

/*** three ***/
ods tagsets.excelxp  OPTIONS(SHEET_NAME="PRODUCT_LOCATION_SKU");
TITLE "REPORT THREE FOR JIM THOMPSON BY PRODUCT, LOCATION AND SKU, FOR DATES &wk_begin. THROUGH &wk_start.";
proc report data=gspe.start6A&rpttype.

/*** four ***/
ods tagsets.excelxp  OPTIONS(SHEET_NAME="RED_RANK_ROWS");
TITLE "REPORT FOUR ROWS WITH A RED RANKING, FOR DATES &wk_begin. THROUGH &wk_start. ";
proc print data=work.REDROWS noobs;
run;

/*** five ***/
ods tagsets.excelxp  OPTIONS(SHEET_NAME="SEV_SMPL_SIZES_CODING");
TITLE "SUPPLEMENT ONE: SEVERITY SAMPLE SIZES CODING AND PROC FORMATS USED FOR THIS REPORT. FOR DATES &wk_begin. THROUGH &wk_start.";
proc print data=work.formatslist noobs;
run;
/*** close and clear ***/
ODS tagsets.excelxp CLOSE; 
title;
footnote;