Getting error when I open excel file created from stored process using ODS TAGSETS.EXCELXP

[bmsampath]

Hi ,

I am getting the following error when I open the excel created from stored process using ods tagsets.excelxp.

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

If the output is created as HTML, it is opening without any issues.

Please let me know what is the issue here.

Thanks,

Sampath

[rogerjdeangelis]

HTML seems a little more savy than EXCEL. I have seen this issue when cells contain '<', '>', or leading '='.
'=' triggers a function or formula?

Also I have found the error is often assocated with compute blocks in proc report. You can try turnining off the compute bloacks/

[Cynthia_sas]

Hi:
You did not say how you were creating the TAGSETS.EXCELXP output and what client application was trying to open the output. Typically, if you were using the SAS Add-in for Microsoft Office, depending on your version of the SAS BI Platform, you might have issues opening ExcelXP output using the stored process in Excel.

Do you use the STPSRV_HEADER function to set the result type? Are you using the Stored Process Web Application to run the stored process?

You might want to check the Stored Process forum because there have been many postings about using TAGSETS.EXCELXP like this one
https://communities.sas.com/t5/SAS-Stored-Processes/Stored-process-and-ExcelXP-tagset-error-and-odd-...

cynthia

[bmsampath]

This is more of data issue than stored process issue.

This excel is opening when we extract data for the date range(parameter) 01Jan2014 to 31Dec2016. Not opening for the date range 01Jan2014 to 31Dec2014.

I have run the same code in EG and getting the same error when I open  the excel file.

I am using the below for creating the report.

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;

Please let me know if you find any issue in this code.

Thanks,
Sampath

[bmsampath]

hi,

I found out that I am getting this error due to the following lengthy text(3430 chars) in one of column(year2012) in proc report.

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

How can I solve this issue?

Thanks,
Sampath

[jimbarbour]

Sampath,

How are you identifying that data as the cause of the problem?  If you were to delete that entire row, does the file open OK in Excel? 

The reason I ask is that Excel has a limit of 32,767 characters per cell.  3,430 characters shouldn't be a problem. 

Jim