ERROR: The position is out of range.

[ding_dong]

Hi

I'm using the excelxp tagsets to create an excel spreadsheet with multiple sheets. Unfortunately, whilst creating the sheets, I encounter:

ERROR: The position is out of range.

NOTE: The above message was for the following by-group:
loc=Other

I've never come across this error before. What does it typically point to? I've excluded the 'Other' group and everything runs fine.

Regards

[Cynthia_sas]

Hi:
I suspect that there's something about the "Other" by group or group that is incompatible with options or sub-options you have specified. Without looking at your code -- at least the complete invocation statement, that's only a guess.

You might wish to open a track with Tech Support for more complete help. They can look at your data and look at your code and help you find a resolution.

To send a question to Tech Support, go to http://support.sas.com/ and in the left-hand navigation pane, click on the link entitled "Submit a Problem". Alternately, you can go directly to the Tech Support Problem Form here:
http://support.sas.com/ctx/supportform/createForm

cynthia

[ding_dong]

Hi Cynthia

Thanks for the response. Unfortunately, the 'Other' group was something of a red herring. However, I've managed to pinpoint the issue. If I take out the nozero option from the define statement (bbi_solus), I no longer receive the error. My next question is, are there limitations to the nozero option when used in combination with nesting variables. Some of the levels of the nesting variable are populated and some are blank. Would this cause issues when using the nozero option? I pasted below a copy of the code I'm running.

Thanks in advance.


proc report data=caps nowd split='#';
title1 "Customer Matrix: #byval(loc)";
title2 h=14pt bcolor=white color=blue underlin=1 link="#'Summary'!A4" "Return to Menu Page";
where grp in ('Sales','Installs')
and reportgrp="&&grp&i";
by loc;
column manager
name
('' grp),
('' type),
(new_cust_triple
new_cust_combo
solus
solus_bbi
solus_tel
solus_dig
new_cust_combo_wlr
new_cust_solus_wlr);
define manager / 'Manager'
style(column)={cellwidth=220}
group;
define name / 'Agent'
style(column)={cellwidth=220}
group;
define grp / ''
order=data
across;
define type / ''
across;
define new_cust_triple / 'Triple'
style(column)={cellwidth=95}
nozero
analysis sum;
define new_cust_combo / 'Combo'
style(column)={cellwidth=95}
analysis sum;
define solus / 'Solus'
nozero
style(column)={cellwidth=95}
analysis sum;
define solus_bbi / 'Solus BBI'
style(column)={cellwidth=95}
nozero
analysis sum;
define solus_tel / 'Solus Telco'
style(column)={cellwidth=95}
nozero
analysis sum;
define solus_dig / 'Solus DTV'
style(column)={cellwidth=95}
nozero
analysis sum;
define new_cust_combo_wlr / 'Combo#Line#Rental'
style(column)={cellwidth=95}
nozero
analysis sum;
define new_cust_solus_wlr / 'Solus#Line#Rental'
style(column)={cellwidth=95}
nozero
analysis sum;
run;

[Cynthia_sas]

Hi:
You might wish to work with Tech Support on this issue. This note explains that NOZERO is ignored by all destinations except for LISTING destination.
http://support.sas.com/kb/31/262.html

I no longer have 9.1.3 to test with...so if you are using NOZERO in SAS 9.1.3, Tech Support will have to answer your question about how NOZERO might impact TAGSETS.EXCELXP output in SAS 9.1.3. My understanding is that if you are using SAS 9.2, that NOZERO is ignored except for LISTING.
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/a002473627.htm#a003072103 (references to "monospace output" means the LISTING destination in the documentation)

Again, if you are using SAS 9.1.3, you may want to work with Tech Support on this issue.

cynthia

[Tim_SAS]

Hi, Cynthia,

You are right about NOZERO being broken for MARKUP output in 9.1.3. After a short disappearance, NOZERO is again available (and not broken) for MARKUP output in 9.2.