The SAS Output Delivery System and reporting techniques

what is the maximum allowed length for a string when writing excel using ods excel?

Reply
Contributor
Posts: 32

what is the maximum allowed length for a string when writing excel using ods excel?

Hi,

 

 

I am getting the following error when I open the excel file due to lengthy text(3430 chars) in one of column(year2012) in proc report.

 

Problem during load

Problems came up in the following areas during load 'Table';

This file annot be opened because of errors. Errors arelisted in:
C:\Users\Sam\Appdata\Local\Microsoft\Windows\Temporary Internet file\COntent.MSO\7B7DF29D.log

 

Up to 293 char, Excel is opening with out any issues. When the length crosses 193 , excel is not opening and throwing the above error.

 

Code used:

 

PROC REPORT data=REPORT_DS split="*" headskip style(header)={background=cx113388 color=cxffffff};

BY CEDENT_DETAILS REINS_PROG_ID BUS_ID BUS_TTL LAYER_NO INCEP_DATE CANC_DATE BROKER_DETAILS PROP_NOPROP ;

column sort_order name YEAR&yr3 YEAR&yr2 YEAR&yr1 ;

define sort_order/ORDER noprint;

define name/"UWY" DISPLAY;

define YEAR&yr1./" &yr1 " &stp_format_lead_zero. ;

define YEAR&yr2./" &yr2 " &stp_format_lead_zero. ;

define YEAR&yr3./" &yr3 " &stp_format_lead_zero. ;

compute NAME ;

if NAME="Underwriting result" then call define(_row_,'style','style=[font_weight=bold]');

endcomp;

RUN;

 

Could you please let me know what is the maximum allowed length for a string when writing excel using ods excel?

 

Thanks,
Sampath

Grand Advisor
Posts: 9,694

Re: what is the maximum allowed length for a string when writing excel using ods excel?

It helps to post the log of the code run along with the error message. The postion of error messages can be important in diagnosing problems. Also paste into the code box using the {i} icon to preserve formatting.

 

It usually helps when involving macro variables to have option mprint symbolgen in effect.

Your &stp_format_lead_zero might be contributing to the issue but we have absolutely no idea what it contains.

Contributor
Posts: 32

Re: what is the maximum allowed length for a string when writing excel using ods excel?

The error I mentioned is coming when I open the excel created by ODS excel.

 

&stp_format_lead_zero is used format the values with leading zeroes. It is created by our internal standard macro.

 

 

SAS Super FREQ
Posts: 8,641

Re: what is the maximum allowed length for a string when writing excel using ods excel?

Hi:

  My tendency is to recommend that you open a track with Tech Support. I ran this program using ODS EXCEL in SAS 9.4 and as you can see in the screen shot below, the length of my character variable is over 3000 characters and Excel opened the file just fine:

ods_excel_very_large_char_var.png

 

Here's the code that created the above file:

** just a huge text string to test. it is in chunks which will be joined together in the ;
** data step program below;
%let l01 = %str( Beginning of Long String:);
%let l02 = %str(  abcdefg, hijklmnop, qrstuv, wxyz. abcd 12345 efghijklm 67890 nopq 123456 rstuv wxyz);
%let l03 = %str(  Twas brillig and the slithy toves did gyre and gimble in the wabe);
%let l04 = %str(  Lorem ipsum quia dolor sit amet, consectetur, adipisci velit. Lorem ipsum quia dolor sit amet, consectetur, adipisci velit.);
%let l05 = %str(  Lorem ipsum dolor sit amet, consectetur adipiscing elit. Duis tincidunt lectus augue, ut bibendum tellus ullamcorper lacinia.);
%let l06 = %str(  Integer consequat tincidunt tincidunt. Sed mollis id lacus vel venenatis. Donec nulla urna, commodo sed blandit eu, auctor ut mi.);
%let l07 = %str(  Lorem ipsum dolor sit amet, consectetur adipiscing elit. Duis tincidunt lectus augue, ut bibendum tellus ullamcorper lacinia.);
%let l08 = %str(  Twas brillig and the slithy toves);
%let l09 = %str(  Did gyre and gimble in the wabe);
%let l10 = %str(  All mimsy were the borogroves and the momeraths outgrabe);
%let l11 = %str(  Beware the Jabberwock by son, the jaws that bite, the claws that snatch);
%let l12 = %str(  Beware the JubJub Bird and shun the frumious Bandersnatch);
%let l13 = %str(  Integer consequat tincidunt tincidunt. Sed mollis id lacus vel venenatis. Donec nulla urna, commodo sed blandit eu, auctor ut mi.);
%let l14 = %str(  Vestibulum vitae velit vitae erat rhoncus imperdiet. Curabitur feugiat lacinia arcu nec laoreet. Donec placerat aliquet nibh, in volutpat justo porttitor nec.);
%let l15 = %str(  Sed lorem mi, hendrerit et cursus quis, mollis in tortor. Phasellus vitae sem quis urna ultricies ullamcorper. Nullam rutrum sed tortor eget pellentesque.);
%let l16 = %str(  Lorem ipsum dolor sit amet, consectetur adipiscing elit. Duis tincidunt lectus augue, ut bibendum tellus ullamcorper lacinia.);
%let l17 = %str(  Integer consequat tincidunt tincidunt. Sed mollis id lacus vel venenatis. Donec nulla urna, commodo sed blandit eu, auctor ut mi.);
%let l18 = %str(  Ut et dapibus lorem, tempus ultricies libero. Praesent ultricies augue vitae tortor pulvinar cursus. Etiam sed facilisis lacus, a dapibus risus.);
%let l19 = %str(  In eget felis tincidunt, consectetur velit at, lacinia leo. Sed et metus vitae leo sodales interdum id ac ligula. Integer sed orci sit amet ante pretium sagittis at a erras.);
%let l20 = %str(  Curabitur vitae ultricies elit. Duis laoreet ac odio aliquam consectetur. Vestibulum consectetur enim sit amet ante hendrerit, ac convallis elit luctus.);

options noquotelenmax;
data testlong;
  length big_char_var $5000 ;
  set sashelp.class(keep=name age);
  if _n_ le 3;
  big_char_var="&l01.&l02.&l03.&l04.&l05.&l06.&l07.&l08.&l09.&l10.&l11.&l12.&l13.&l14.&l15.&l16.&l17.&l18.&l19.&l20.&l08.&l09.&l10.&l11.&l12.&l02.&l03.&l04.&l05.&l06.&l07.&l08.&l09.&l10.&l11.&l20";
  lgbig = length(big_char_var);
  putlog _n_= name= lgbig= big_char_var $50.;
run;

title; footnote;
ods excel file="c:\temp\testbig.xlsx" ;
  proc report data=testlong;
  column name age lgbig big_char_var;
  define big_char_var / style(column)={cellwidth=12in};
  run;
ods excel close;

So there may be something in how you are defining or submitting the stored process that is causing the issues you are seeing. When I use Base SAS with a very, very long text string, ODS EXCEL does not have any problems with the file.

 

cynthia

Contributor
Posts: 32

Re: what is the maximum allowed length for a string when writing excel using ods excel?

Hi,

 

I am getting this error due to the following text in one of the variabe in proc report. Do you see any issue in the following text?

 

Accounting Professions,,Agricultural Crop Hail,Agricultural MPCI (Multi Peril Crop Insurance),Agriculture Livestock, Bloodstock and Epizootic Insurance,Air Cargo,All Around-Protection /Combined Product,All Risk Commercial - BI only,All Risk Commercial - PD only,All Risk Industrial - BI only,All Risk Industrial - PD only,Annuity Insurance,Architects and Engineers PI,Atomic Facility-Property Insurance,Aviation - Hull - GA,Aviation - Hull - Major Risks,Aviation - Hull - unspecified,Aviation - Liability - GA,Aviation - Liability - Major Risks,Aviation - Liability - unspecified,Aviation - Personal Accident - unspecified,Aviation - Terror Liability,Bankers Blanket Bond,Bonds,Build Operat Transfer,Burglary,CAR-Terror,Cargo DSU,Carriers Liability,Cash Box Insurance,Cash value Life Insurance,Civil Engineering Completed Risk - BI only,Civil Engineering Completed Risk - MD only,Clinical Trial,Compr. Building - Private,Comprehensive Household,Construction Plant & Equipment,Contractors All Risks (CAR) - ALOP only,Contractors All Risks (CAR) - MD only,Credit,Decennale,Directors and Officers,Disability Rider,Dread Disease,Dynamic Property - Commercial,EAR-Terror,Earthquake,Electronic Equipment - BI only,Electronic Equipment - PD only,Electronic Equipment Insurance Data Media,Employer's Liability,Employment Related Practices,Energy Industrial All Risk(EIAR) BI,Energy Industrial All Risk(EIAR) MB,Energy Industrial All Risk(EIAR) MLOP,Energy Industrial All Risk(EIAR) PD,Energy OffShore Operators Extra Expense,Engineering TPL,Environmental Liability,Erection All Risk (EAR) - ALOP only,Erection All Risk (EAR) - MD only,Exhibition,Extented Coverage - BI only,Extented Coverage - PD only,Fidelity,Fire Agriculture (Buildings/Equipment),Fire Commercial - BI only,Fire Commercial - PD only,Fire Industrial - BI only,Fire Industrial - PD only,Fire Terror - BI only,Fire Terror - PD only,Fire Third Party Liability,Flood,Former Comprehensive Household (DV),General Third Party Liability,Glass,Group Life Insurance,Guarantee,Hail,Health Care,Inherent Defect Indemnity,Kidnapping/Ransom,Land Cargo,Legal Professions,Legal Protection,Liability Nuclear,Life Risk (Non Part. w/o O&G),Long Term Care,Luggage,Machinery Breakdown - BI only,Machinery Breakdown - PD only,Marine Cargo - Terror,Marine Cargo - unspecified,Marine Hull - Terror,Marine Hull - unspecified,Marine Technology (offshore),Marine War,Medical Malpractice,Motor Extended Warranty,Motor Full Own Damage,Motor Partial Own Damage,Motor Personal Accident,Motor Third Party Liability,Ocean Cargo,Ocean Hull,Omnium Insurance,Other Liability,Other Marine Hull outside other LOB,Other Natural Perils,Other Pecuniary Losses,Other Property - BI only,Other Property - PD only,Pension and Trustee Liability,Personal Accident - Rider,Personal Accident - Sports only,Personal Accident - Terror only,Personal Accident - Travel Insurance,Personal Accident Group,Personal Accident Individual,Pleasure Craft,Political Risk,Product Liability,Product Recall,Professional Liability - other Professions,Property Assistance Insurance,Public Liability,River Cargo,River Hull,Roadside Assistance-Coverage,Shipbuilder Risk,Space Hull,Space Liability,Space Pecuniary Losses,Special Risks non-BI different from other Marine Classes,Term Life Assurance,Terror Liability,UPR - PA with Premium Refund,Valuables - Bank Cards,Valuables - Jewellery,Valuables - other,Water Damage,Windstorm,Workers Compensation Act

 

Thanks,

Sampath

SAS Employee
Posts: 263

Re: what is the maximum allowed length for a string when writing excel using ods excel?

Are you using "ods Excel ..." or "ods tagsets.ExcelXP ..."?  What is in the error log written by Excel when you attempt to open the file (in the Content.MSO directiory - see your initial post).

 

Vince DelGobbo

SAS R&D 

Contributor
Posts: 32

Re: what is the maximum allowed length for a string when writing excel using ods excel?

I am using ods tagset.excelxp.

 

I see the below message in log file.

 

 

XML ERROR in Table
REASON: Bad Value
FILE: C:\Users\re00568\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.MSO\B6B90715.xls
GROUP: Table
TAG: Row
ATTRIB: Height
VALUE: 20444

XML ERROR in Table
REASON: Bad Value
FILE: C:\Users\re00568\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.MSO\B6B90715.xls
GROUP: Table
TAG: Row
ATTRIB: Height
VALUE: 10336

XML ERROR in Table
REASON: Bad Value
FILE: C:\Users\re00568\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.MSO\B6B90715.xls
GROUP: Table
TAG: Row
ATTRIB: Height
VALUE: 20444

XML ERROR in Table
REASON: Bad Value
FILE: C:\Users\re00568\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.MSO\B6B90715.xls
GROUP: Table
TAG: Row
ATTRIB: Height
VALUE: 10336

SAS Employee
Posts: 263

Re: what is the maximum allowed length for a string when writing excel using ods excel?

Please report this to our Technical Support Department using this form:

 

http://support.sas.com/ctx/supportform/createForm

 

Vince DelGobbo

SAS R&D

SAS Super FREQ
Posts: 8,641

Re: what is the maximum allowed length for a string when writing excel using ods excel?

Hi:
My program shows you that the text itself is not the issue. Your character string, with spaces is 3344 characters; my string is 3399. Both of those strings are over the 193 limit you posted in this sentence "Up to 293 char, Excel is opening with out any issues. When the length crosses 193 , excel is not opening and throwing the above error." I just ran a test with a length over 4000 and ODS EXCEL still opened the file correctly. Vince_SAS has suggested you look at the log file that is created when you try to open this file. You also indicated that you are using ODS EXCEL and my test shows that ODS EXCEL does not have an issue with a long variable string. So that means your recourse is to investigate the log and possibly do more testing, and probably work with Tech Support on this issue. Posting your long text over and over won't net you more constructive suggestions because ODS EXCEL in several tests will open sheets with very long variables from 193 - 4260. So the text itself is not the issue. It is either the destination, the client application running the stored process or something else that is going wrong when the file is rendered after the stored process is executed.

cynthia
Contributor
Posts: 32

Re: what is the maximum allowed length for a string when writing excel using ods excel?

Hi, I am using ods tagset.excelxp. I see the below message in log file. XML ERROR in Table REASON: Bad Value FILE: C:\Users\re00568\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.MSO\B6B90715.xls GROUP: Table TAG: Row ATTRIB: Height VALUE: 20444 XML ERROR in Table REASON: Bad Value FILE: C:\Users\re00568\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.MSO\B6B90715.xls GROUP: Table TAG: Row ATTRIB: Height VALUE: 10336 XML ERROR in Table REASON: Bad Value FILE: C:\Users\re00568\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.MSO\B6B90715.xls GROUP: Table TAG: Row ATTRIB: Height VALUE: 20444 XML ERROR in Table REASON: Bad Value FILE: C:\Users\re00568\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.MSO\B6B90715.xls GROUP: Table TAG: Row ATTRIB: Height VALUE: 10336 Thanks, Sampath
Contributor
Posts: 32

Re: what is the maximum allowed length for a string when writing excel using ods excel?

SAS Employee
Posts: 263

Re: what is the maximum allowed length for a string when writing excel using ods excel?

Please report this to our Technical Support Department using this form:

 

http://support.sas.com/ctx/supportform/createForm

 

Vince DelGobbo

SAS R&D

Contributor
Posts: 32

Re: what is the maximum allowed length for a string when writing excel using ods excel?

Contributor
Posts: 32

Re: what is the maximum allowed length for a string when writing excel using ods excel?

Reported this issue to Tech support.

 

Thanks,

Sampath

Post a Question
Discussion Stats
  • 13 replies
  • 227 views
  • 0 likes
  • 4 in conversation