DATA Step, Macro, Functions and more

ExcelXP tagset

Reply
Super Contributor
Posts: 673

ExcelXP tagset

I read documentation and tried the following and there is no error in the log,but when i try to open the excel sheet it gives an error message "Unable to read file".

ODS TAGSETS.EXCELXP FILE='C:\tagset2.xls';
proc report data=whole_v2 nowd NOWINDOWS HEADLINE MISSING;
COLUMN terr_id bsm physician_name total_scripts, year, productgroup;
define terr_id/group;
define bsm/group;
DEFINE physician_name / group 'PhyName';
DEFINE year / across '';
DEFINE productgroup / across '';
DEFINE total_scripts / sum format=comma12. ' ';
break after terr_id/summarize skip ol style=[font_weight=bold background=red];
run;
ODS TAGSETS.EXCELXP CLOSE;
Super Contributor
Super Contributor
Posts: 3,174

Re: ExcelXP tagset

You're going to need to share your SAS log output for help, to include the surrounding SAS code executed for your program.

Scott Barry
SBBWorks, Inc.
SAS Super FREQ
Posts: 8,862

Re: ExcelXP tagset

Hi:
Here are some things you can try:
1) make sure you have Excel 2002 or higher (Office 97 or 2000) will not open the type of XML created by TAGSETS.EXCELXP;

2) make sure you have downloaded and installed the most current TAGSETS.EXCELXP tagset template definition, as described here:
http://support.sas.com/kb/32/394.html

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.


cynthia

My test code:
[pre]
data whole_v2;
set sashelp.prdsale;
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);
run;

ODS TAGSETS.EXCELXP FILE='C:\temp\tagset2.xml'
style=sasweb;
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 bsm/group;
DEFINE physician_name / group 'Phy/Name';
DEFINE year / across ' ';
DEFINE productgroup / across ' ';
DEFINE total_scripts / sum format=comma12. ' ';
break after terr_id/summarize
style=[font_weight=bold background=red];
run;
ODS TAGSETS.EXCELXP CLOSE;
[/pre]
Super Contributor
Posts: 673

Re: ExcelXP tagset

Posted in reply to Cynthia_sas
Cynthia,
I followed the steps you mentioned and yet I get the message "unable to read file".

Thanks,
SASPhile
SAS Super FREQ
Posts: 8,862

Re: ExcelXP tagset

Hi:
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.

To open a track with Tech Support, go to:
http://support.sas.com/ctx/supportform/createForm

cynthia
Super Contributor
Posts: 673

Adding columns to Proc tabulate

Posted in reply to Cynthia_sas
Cynthia,
The following code for proc tabulate works;
proc tabulate data=whole_v2 missing;
class year year_month productgroup terr_id bsm md_zip Physician_name;
var total_scripts;
table terr_id* bsm*md_zip*Physician_name all='Montly total'*{style={font_weight=bold background=red}}, year=' '*(year_month=' ')*productgroup=' '*total_scripts=' '*sum all='2007,2008,2009 total'*productgroup=' '*total_scripts=' '*sum /row=float format_precedence=row; keylabel sum=' ';
run;


But as per new requirement, I would have to add addr1,addr2, city and state next to physician name.

I tried adding these columns with and without commas, it is giving me an error.
Is there any workaround for this kind of situations?
Valued Guide
Posts: 2,177

Re: ExcelXP tagset

When I get that "unable to read file" message, it usually means
1
no data for the proc, but the ODS xml/xls file was created, and containing no
it would not open.

2
I forgot the style=
or
3
another mistake on my part
Then a supplementary log reports the error in my xml in a log file in "hidden" folder C:\Temp\Internet\Content.MSO.

It was always a problem I caused.

good luck

PeterC
Super Contributor
Super Contributor
Posts: 3,174

Re: ExcelXP tagset

What error and what did your revised SAS code look like when you added the new variables? This info would be most helpful rather than guessing or making any assumptions about syntax correctness.

Scott Barry
SBBWorks, Inc.
Super Contributor
Posts: 673

Proc Tabulate

This is the working code:
proc tabulate data=whole_v2 missing;
class year year_month productgroup terr_id bsm md_zip Physician_name ;
var total_scripts;
table terr_id* bsm*md_zip*Physician_name all='Montly total'*{style={font_weight=bold background=red}}, year=' '*(year_month=' ')*productgroup=' '*total_scripts=' '*sum all='2007,2008,2009 total'*productgroup=' '*total_scripts=' '*sum /row=float format_precedence=row; keylabel sum=' ';
run;
-------------------------------------------------------------------------------
Changes made:
I added four variables after physician_name in the class statement and in the table statement
proc tabulate data=whole_v2 missing;
class year year_month productgroup terr_id bsm md_zip Physician_name md_addr1 md_addr2 city md_state;
var total_scripts;
table terr_id* bsm*md_zip*Physician_name * md_addr1 *md_addr2* city* md_state all='Montly total'*{style={font_weight=bold background=red}}, year=' '*(year_month=' ')*productgroup=' '*total_scripts=' '*sum all='2007,2008,2009 total'*productgroup=' '*total_scripts=' '*sum /row=float format_precedence=row; keylabel sum=' ';
run;

ERROR: Limit of 2147483647 potential interactions within a crossing exceeded for the table statement at line 682.

I'm wondering if there is any possible circumvention to this problem
Valued Guide
Posts: 2,177

Re: Proc Tabulate

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.

Good Luck

PeterC
Frequent Contributor
Posts: 102

Re: Proc Tabulate

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).
Valued Guide
Posts: 2,177

Re: Proc Tabulate

Posted in reply to JackHamilton
Jack

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.

PeterC
Ask a Question
Discussion stats
  • 11 replies
  • 1835 views
  • 0 likes
  • 5 in conversation