topic Re: Create an ERROR because of wider Column width. in ODS and Base Reporting

Create an ERROR because of wider Column width.

patel1987 — Mon, 26 Dec 2011 18:10:21 GMT

Hello,

Here I pulled data from database and Column2 has lenght $255. and both coulmns are character. When I give ouput in excel then it create an error. File is not open in excel.

If, I drop wider Column (COLUMN2) from dataset then its not create an error. But when again to keep it in dataset error comes up.

I am using the following options:

ODS TAGSETS.EXCELXP file='c:\temp\filename.xml' style=sasweb

ods tagsets.ExcelXp options( embedded_titles='yes'

EMBED_TITLES_ONCE='yes'

CONVERT_PERCENTAGES='yes

sheet_interval='none'

orientation='landscape'

pagebreaks='yes'

doc='help'

zoom='60'

ABSOLUTE_COLUMN_WIDTH='28,255'

DEFAULT_COLUMN_WIDTH='25'

wraptext='yes'

autofit_height='yes'

);

PROC REPORT data= X;

Column COLUMN1 COLUMN2;

Define Coulmn1/ Display left;

Define Column2/ Display left;

run;

ods tagsets.ExcelXp close;

Thanks

Re: Create an ERROR because of wider Column width.

Cynthia_sas — Tue, 27 Dec 2011 16:47:14 GMT

Hi:

Thanks for posting this in the ODS forum instead of the stored process forum. It didn't seem like a stored process-related question.

I think that you may have a problem with your version of TAGSETS.EXCELXP or else some other kind of problem, perhaps with your data, or perhaps with your version of Excel. You may really need to work with Tech Support on this issue.

I can create a REALLY big character variable (more than 255 characters) and use TAGSETS.EXCELXP and PROC REPORT to create an output report using the relevant options, such as ABSOLUTE_COLUMN_WIDTH and AUTOFIT_HEIGHT. My version of TAGSETS.EXCELXP (which you can find in your SAS log) is:

NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.122, 01/04/2011).

and, if you are running an older version of TAGSETS.EXCELXP template, this Tech Support note shows how to update your ODS MARKUP tagset template item store: http://support.sas.com/kb/32/394.html

The attached screenshots show my code and part of the output when the XML file is opened in Excel. I really think you need to work with Tech Support on this question.

cynthia

Re: Create an ERROR because of wider Column width.

patel1987 — Tue, 27 Dec 2011 19:28:11 GMT

Thanks. But I am using SAS eg and excel 2003. I am also try to follow your code but it is getting same error.

Thanks

Create an ERROR because of wider Column width.

Cynthia_sas — Tue, 27 Dec 2011 19:55:05 GMT

Hi:

My screen shot was made using SAS 9.3 and Excel 2010 -- so the issue must be something between your version of SAS and your version of Excel. If you are using EG you could be using either SAS 9.1.3 or 9.2 or 9.3 -- but this is another reason why you should work with Tech Support. In the latest version of SAS, with the most current version of Excel, I do not get an error. Tech Support can try to replicate your error using the same version of EG, SAS and Excel. It is possible that the issue is with Excel and how it renders the XML -- in which case, upgrading to a newer version of Excel may fix the problem. But Tech Support can tell you for sure.

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