Adding a tab / sheet to an existing workbook / .xlsx file

[GregG]

I have a dataset that has multiple years of data.

A report is created for each year and is sent to Excel.

Currently, each year is creating an entirely new workbook.

First goal is creating one workbook with each year getting its own tab/sheet.

Second goal is to add a tab/sheet to the existing workbook for each future year's report.

What are my options, if any, for doing these things?

I consider myself to be extremely new to this, and know only the bare basics about proc export.

Thank you for your time.

[LinusH]

How does your report program look like?

If each years of data is stored in a separate table, you could probably "right off" use the libname excel engine instead of proc export. Here, each sheet is considered (logically) as a data set from SAS point of view.

[Vish33]

Use ODS Tagsets.ExcelXP to send each years report to a different tab and macrotize it.

[Vish33]

Here is a sample code , further you can create macro variables to create your tab sheets dynamically wit years.

data years;

input year country $ sales @@;

cards;

2011 US 20000 2011 IN 30000 2011 SA 40000

2012 US 30000 2012 IN 40000 2012 SA 50000

2013 US 30000 2013 IN 40000 2013 SA 50000

;

run;

ods listing close;

ods tagsets.ExcelXP file="/sasdata/sample.xls"

options (embedded_titles='yes' suppress_bylines='yes'

Orientation='Landscape' sheet_name="2011"

fittopage='yes' Pages_FitWidth ='1'

Pages_FitHeight = '1' frozen_headers='1');

proc report data=years;

where year in (2011);

run;

ods tagsets.ExcelXP

options (embedded_titles='yes' suppress_bylines='yes'

Orientation='Landscape' sheet_name="2012"

fittopage='yes' Pages_FitWidth ='1'

Pages_FitHeight = '1' frozen_headers='1');

proc report data=years;

where year in (2012);

run;

ods tagsets.ExcelXP

options (embedded_titles='yes' suppress_bylines='yes'

Orientation='Landscape' sheet_name="2013"

fittopage='yes' Pages_FitWidth ='1'

Pages_FitHeight = '1' frozen_headers='1');

proc report data=years;

where year in (2013);

run;

ods tagsets.ExcelXP close;

ods _all_ close;

ods listing;

[GregG]

Thank you so much.

So, I used your code above (but changed the file= to a different location) and it writes the file (I see it appear in the directory) - but it will not open.

No error in the SAS log, but when opening the .xls file, I get this:

"The file you are trying to open, 'sample.xls', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"

[Yes] [No] [Help]

Selecting [Yes] yields Unable to read file.

I am using Windows 7 64bit and SAS 9.3 64bit. Office is 32bit. I know with proc export, I had to make a change for it to work with these settings.

I have never used (or heard of) ods tagsets.excelXP until trying to solve this problem.

Thank you for your help.

[Reeza]

Tagsets create XML files not native XLSX files and once created cannot be changed in the future by SAS directly, ie you can't add on an extra tab.

Have you tried exporting, standard export using DMBS=EXCELCS? You also need to make sure you have the SAS PC Files Server installed and running to work around the bitness difference.

[GregG]

@Reeza - thank you. I have no experience with tagsets. I have successfully written to 32bit Excel using the code below.

This is what I was trying - which created an xlsx file that I could open, but with only one sheet (the last one).

%macro test (var=,sheet=);

proc export    data=sashelp.class (where=(sex="&var"))

                    outfile="C:\temp.xlsx"

                    dbms=xlsx

                    replace;

                    sheet="&sheet";

run;

%mend test;

%test(var=F,sheet=Female);

%test(var=M,sheet=Male);

[Reeza]

If you change the DBMS to EXCELCS?

I get no errors in the log, but a file that doesn't open

[GregG]

If I change dbms=xlsx to dbms=excelcs, I get log error: failed to connect to server: .

When I researched this, it was mentioned that I could sub xlsx instead to solve this problem.

I don't know if I am causing other issues by doing so. Ideally I would like to be able to write to the same file (in a new tab) each subsequent year. But I haven't found anything that leads me to that yet.

Thank you for your help!

[Reeza]

That means you need to install or start the PC Files Server, it should be under Start>All Programs >SAS>PC File Server

[GregG]

I had to download and install the SAS PC Files Server.

Changed the DBMS to EXCELCS.

No errors in the log.

File won't open. (Excel cannot open the file 'temp.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.)

ETA: the file is twice the size (10 KB instead of  5 KB), so I guess it is possible that it is writing both tabs - but I still can't open the file.

[Reeza · Accepted solution]

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

Hotfix that you need to install.  I have no idea, just fishing, because really I have the same issue, but can't try it because I don't have install priveleges.

If you can live with XLS file the following works:

%macro test (var=,sheet=);

proc export    data=sashelp.class (where=(sex="&var"))

                    outfile="C:\_localdata\temp.xls"

                    dbms=EXCELCS

                    replace;

                    sheet="&sheet";

run;

%mend test;

%test(var=F,sheet=Female);

%test(var=M,sheet=Male);

[GregG]

Thank you so much!

That works for me to, and appears to update correctly (running a 3rd call separately from the first two).

[GregG]

No I need to figure out if something similar can be done other ways.

The person in my office needing this is using ODS HTMLCSS to output formatting.

PROC EXPORT is just the dataset from what I can tell.

[Reeza]

Was that the hotfix or XLS option?

My issue with the XLS option is that you still have row limits and some of my data is over 100K rows.