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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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);

View solution in original post

15 REPLIES 15
LinusH
Tourmaline | Level 20

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.

Data never sleeps
Vish33
Lapis Lazuli | Level 10

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

Vish33
Lapis Lazuli | Level 10

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
Quartz | Level 8

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

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
Quartz | Level 8

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

If you change the DBMS to EXCELCS?

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

GregG
Quartz | Level 8

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

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

GregG
Quartz | Level 8

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

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
Quartz | Level 8

Thank you so much!

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

GregG
Quartz | Level 8

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

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. Smiley Sad

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
  • 15 replies
  • 11075 views
  • 3 likes
  • 4 in conversation