Position Out of Range

[Anuj_Gupta]

Hi,

I'm trying to create an Excel workbook with multiple sheets using tagsets, but I'm getting "Position out of Range" error?

I'm not able to find any solution to this. Can anyone please let me know the solution for this problem.

Thanks,
Anuj

[Peter_C]

code, or sasLog

[UCFAngel]

I am having this problem too but it doesn't happen all the time. I've created a SAS stored process that outputs an Excel file through _webout. Depending on what the user selects from the form, it either creates the file successfully or when I click to open the file I get a Workbook error. When I review the log, that is where I see "ERROR: The position is out of range".

I am using SAS 9.1.3 and creating a .xls file which opens in Excel 2007 when I click "Yes" after the warning about the file format.

[Cynthia_sas]

Hi:
It really depends HOW you are creating the file for Excel in your stored process. Are you using ODS HTML? Are you using ODS MSOFFICE2K? Are you using ODS TAGSETS.EXCELXP? Also, since you are using a stored process and _webout, do you have the correct STPSRV_HEADER or content-type header for the stream.

If you are using ODS to create your files, then you will need to use a proper content-type header, since ODS creates just an ASCII text file (either HTML or Spreadsheet Markup Language XML) that Excel knows how to open and render.

Sometimes, workbook errors such as you describe happen when you are
1) using TAGSETS.EXCELXP
2) do not have the most current tagset update applied

This frequently is seen when a job will work in a local SAS environment that is running a newer version of SAS but the server (workspace or stored process) is using a earlier version of SAS with a different level of tagset template. Sometimes, I have seen errors when Excel tries to open an HTML or XML file when I have tried to set style attributes incorrectly or inserted some code that might work in HTML, but does not render well when the HTML file is opened in Excel.

Remember that not all client applications on the BI or SAS Enterprise Intelligence Platform can receive output from _WEBOUT -- especially Web Report Studio and the SAS Add-in for Microsoft Office. Especially if you are using TAGSETS.EXCELXP, you cannot execute your stored process from within the Excel pull down menus because TAGSETS.EXCELXP flavor of XML cannot be rendered by the SAS Add-in.

Saying that you use _WEBOUT is a useful piece of information, but it is not the only piece of information needed in this situation. Your best resource for this question might be to work with Tech Support, as they can look at the entire stored process and all your code to help you figure out whether you need to update TAGSETS.EXCELXP on the server or whether you need to do something else (such as use the proper STPSRV_HEADER) in your stored process code.

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

cynthia

[UCFAngel]

I'm sorry for leaving out so many details. I am using the latest version of the ExcelXP tagset. This appears in the SAS log (NOTE: This is the Excel XP tagset (SAS 9.1.3, v1.86, 04/15/08)).

I am using the following to create the header and output:
DATA _NULL_;
RC = STPSRV_HEADER('Content-type','application/vnd.ms-excel;');
RC = STPSRV_HEADER('Content-disposition','attachment; filename=graduate_retention.xls');
RUN;
ods tagsets.excelxp file=_webout style=seaside_ft;

I am running the stored process from the Information Delivery Portal and I use a JSP page to collect the parameters.

The odd thing is that it works sometimes and not others. It seems to depend on which graduate program the user selects for the report.

I thought it might have something to do with the absolute_column_width so I made them wider but that didn't help. The resulting report is only 16 columns wide.

[Cynthia_sas]

Hi:
I still think your best bet is to work with Tech Support on this...too many factors -- tagset version, SAS version, Portal version, suboptions, etc, etc.

Also, I'm just curious whether you're using TAGSETS.EXCELXP suboptions, such as absolute_column_width, etc...don't see it in your code. But Tech Support can help you debug what's happening.

cynthia

[UCFAngel]

Thank you Cynthia - your input is always valued. I will contact Tech Support with this issue. Have a great weekend!

[SASKiwi]

I can easily reproduce this error with the following test program (using SAS 9.1.3 and tagset V1.86):

ods tagsets.excelxp file = "test.xls" style = normal
options (SHEET_NAME = 'Test' DEFAULT_COLUMN_WIDTH = '16'
EMBEDDED_TITLES = 'NO' SHEET_INTERVAL = 'NONE')
;

proc report data = sashelp.class nowindows;
define name / display format = $16. 'Name' ;
define age / display format = 6. 'Age' ;
column name age;
run;

proc report data = sashelp.class nowindows;
define name / display format = $16. 'Name' ;
define age / display format = 6. 'Age' ;
define height / display format = 6. 'Height' style=[cellwidth=90pt];
column name age height;
run;

ods _all_ close;
ods listing;

The error is caused by trying to do a STYLE customisation on a column that is not in the the first table and both tables are being written to the same worksheet. Could this be the same issue for you?

It appears that at least this tagset does not handle STYLE customisations outside of the range created by the first table in the sheet. If you don't customise it works fine.

[SASKiwi]

For peoples benefit the test program above works fine in SAS 9.2 and tagset V1.94.

[UCFAngel]

This was the issue for me. I had to make the columns wider to accommodate the tables below.

Thank you! We are in the process of migrating to 9.2 so it's good to know the tagset will handle these types of problems. Message was edited by: UCFAngel

[SASKiwi]

No problem!

What is interesting when you run tagset V1.94 in SAS 9.2 is you get a message in the SASLOG saying the tagset is compatible with SAS 9.1.3 and above. To my knowledge this version has not been released as a 9.1.3 upgrade even though it says it will work! Is anyone able to shed any light on this?