BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SasGuy614
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
jwillis
Quartz | Level 8

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;

View solution in original post

15 REPLIES 15
slchen
Lapis Lazuli | Level 10

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
Fluorite | Level 6

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
Super User

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
Super User

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
Calcite | Level 5

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
Diamond | Level 26 RW9
Diamond | Level 26

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.

PeteD
Calcite | Level 5

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
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
slchen
Lapis Lazuli | Level 10

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
Fluorite | Level 6

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Lapis Lazuli | Level 10

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

jwillis
Quartz | Level 8

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;

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 15 replies
  • 199968 views
  • 10 likes
  • 9 in conversation