3) After #1 and #2 have been done, retest your code. When I run this code (below) using the most current tagset template definition I -do- get a workbook that opens in Excel 2007. Note that some options, such as HEADLINE, SKIP and OL are ignored by ODS destinations, as they are ONLY used for the LISTING window, so they are not in my code. Also, NOWD and NOWINDOWS are the same option.
4) If this "different format" message is the message you're getting:
The file you are trying to open 'somefile.xls' is in a different format than specified by the file extension. Verify 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
This is a normal message from Office 2007, and you can just click YES to open the file (or name the file with .XML instead of .XLS and you will not get the message). http://support.sas.com/kb/31/956.html
5) If you are getting THIS "not valid" message,
Excel cannot open the file 'somefile.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.
you may have accidentally named the file .XLSX, which is a type of file that is NOT created by TAGSETS.EXCELXP. Again, use .XML instead of .XLSX when you use TAGSETS.EXCELXP.
My test code:
where quarter = 1;
terr_id = substr(division,1,3);
productgroup = substr(product,1,1);
total_scripts = actual / 100;
bsm = region;
physician_name = substr(compress(country,'.'),1,2);
ODS TAGSETS.EXCELXP FILE='C:\temp\tagset2.xml'
proc report data=whole_v2 nowd MISSING split='/';
COLUMN terr_id bsm physician_name
total_scripts, year, productgroup;
define terr_id/group 'Terr/ID';
DEFINE physician_name / group 'Phy/Name';
DEFINE year / across ' ';
DEFINE productgroup / across ' ';
DEFINE total_scripts / sum format=comma12. ' ';
break after terr_id/summarize
ODS TAGSETS.EXCELXP CLOSE;
In that case, your best bet for help is to open a track with SAS Tech Support. I could not duplicate your error condition using the most current version of TAGSETS.EXCELXP and Excel 2007. Tech Support has other versions of Excel to test with and they can look at your data and your code and help you figure out the best solution.
unlike PROC REPORT, tabulate is designed for summary tables - only. Here you want details along with totals. You may want to try PROC REPORT. However, I assume you are using Tabulate because it has special features you need.
I haven't seen all your code, but before your proc tabulate statement, issue the ods statement[pre] ods listing close ;[/pre] It may make a difference because the stats gathering engine in proc tabulate was enhanced for all ods destinations apart from listing. Of course, you won't get the listing output, but there are plenty of others to choose from. The improvement to stats-gathering in listing destination may have been improved in SAS9.2, but it remains in SAS9.1.3 sp4.
While it is true that PROC TABULATE seems designed for summary reports, it will effectively create a detail report if every observation has a unique combination of CLASS variables. You can create a pseudo-OBS variable to ensure uniqueness (preferably in a view to save on I/O).
I too have used Tabulate to generate a details report, but when it brought a fairly effective mainframe to it's knees over just 3500 obs, I started to look for alternatives while TechSupport looked for an explanation. The explanation was to be found in the memory requirements documented in the SAS Guide to Tabulate Processing(probably not the exact title) which clarified that memory was being obtained for an in-memory array with dimensions of the cardinality of the cross-classing requested in the table statement. Since ODS and SAS8, that memory problem only remains for the listing destination. For SASphile's tabulate example the memory requirements would scale approximately according to the product of the counts of unique (formatted) values of terr_id, bsm, md_zip, Physician_name, addr1, addr2, year, year_month, product_group and the number of statistics required at the deepest level. Then it quickly scales up to the limit indicated by the message [pre]ERROR: Limit of 2147483647 potential interactions within a crossing exceeded [/pre].
One circumvention I can see, would concatenate the Physician address lines. They may not be nicely formatted, but post-processing in excel may be the price to pay to get below 2147483647. (and it will also reduce the memory requirements).
When post-processing is to be considered ~ all row-title-class vars could be concatenated into one for proc tabulate (remembering to leave some special character as an internal delimiter between the original class vars).
As I said earlier, or circumvent the whole problem and avoid Tabulate when a details report is required.