ODS ExcelXP Tagset Error in Opening Resulting Excel File

ErinKSimmons — Sat, 24 Nov 2018 02:53:33 GMT

I have pasted below two version of code that I am running (Note that I have only done a portion of the code as there are outputs below and above this chunk so the ods close statements won't be seen).

This is the version of the code I would like to use, as the columns will change from quarter to quarter and I don't want to update this portion of code:

However, when I try to open the resulting Excel file, I get the error:

"Problem during load.

Problem came up in the following areas during load
Table"

But if I use the following code that list out all the variables and define a style for each header the file will open fine.

%macro individual_pl_DEMAND_files(PLName,ProductLine);
data work.Demand_History_Tab_&PLName (drop= product_line);
set work.Demand_History_tab (where=(product_line= &ProductLine.));
run;
** Write out all Demand Lists to XML;
ods tagsets.ExcelXP options( zoom='85' sheet_interval='none' sheet_name="Demand_Tab_&PLName." autofilter='all' frozen_headers='yes');

proc print data=work.Demand_History_Tab_&PLName noobs;
var NIIN / style(data)={tagattr='type:String'} style(header)=[font_weight=bold background=#CCFFCC];
var CAGE_CD / style(data)={tagattr='type:String'} style(header)=[font_weight=bold background=#CCFFCC];
var FSC_CD / style(data)={tagattr='type:String'} style(header)=[font_weight=bold background=#CCFFCC];
var PART_NBR / style(data)={tagattr='type:String'} style(header)=[font_weight=bold background=#CCFFCC];
var NOUN_NM / style(data)={tagattr='type:String'} style(header)=[font_weight=bold background=#CCFFCC];
var UNIT_PRICE_AMT / style(data)={tagattr='type:String'} style(header)=[font_weight=bold background=#CCFFCC];
var ACQ_ADVICE_CD / style(data)={tagattr='type:String'} style(header)=[font_weight=bold background=#CCFFCC];
var AAC_Type / style(data)={tagattr='type:String'} style(header)=[font_weight=bold background=#CCFFCC];
var NIL_Qty / style(data)={tagattr='type:String'} style(header)=[font_weight=bold background=#CCFFCC];
var FY2016QTR1 / style(data)={tagattr='format:#,##0'}style(header)=[font_weight=bold background=#CCFFCC];
var FY2016QTR2 / style(data)={tagattr='format:#,##0'}style(header)=[font_weight=bold background=#CCFFCC];
var FY2016QTR3 / style(data)={tagattr='format:#,##0'}style(header)=[font_weight=bold background=#CCFFCC];
var FY2016QTR4 / style(data)={tagattr='format:#,##0'}style(header)=[font_weight=bold background=#CCFFCC];
var FY2017QTR1 / style(data)={tagattr='format:#,##0'}style(header)=[font_weight=bold background=#CCFFCC];
var FY2017QTR2 / style(data)={tagattr='format:#,##0'}style(header)=[font_weight=bold background=#CCFFCC];
var FY2017QTR3 / style(data)={tagattr='format:#,##0'}style(header)=[font_weight=bold background=#CCFFCC];
var FY2017QTR4 / style(data)={tagattr='format:#,##0'}style(header)=[font_weight=bold background=#CCFFCC];
var FY2018QTR1 / style(data)={tagattr='format:#,##0'}style(header)=[font_weight=bold background=#CCFFCC];
var FY2018QTR2 / style(data)={tagattr='format:#,##0'}style(header)=[font_weight=bold background=#CCFFCC];
var FY2018QTR3 / style(data)={tagattr='format:#,##0'}style(header)=[font_weight=bold background=#CCFFCC];
var FY2018QTR4 / style(data)={tagattr='format:#,##0'}style(header)=[font_weight=bold background=#CCFFCC];
var AverageFYDmd / style(data)={tagattr='format:#,##0'}style(header)=[font_weight=bold background=#CCFFCC];
var CurrDmd / style(data)={tagattr='format:#,##0'}style(header)=[font_weight=bold background=#CCFFCC];
var One_Year_Demand_Estimate / style(data)={tagattr='format:#,##0'}style(header)=[font_weight=bold background=#CCFFCC];
run; quit;

%mend individual_pl_DEMAND_files;
%individual_pl_DEMAND_files(SBPL,'SBPL')
%individual_pl_DEMAND_files(PBPL,'PBPL')
%individual_pl_DEMAND_files(LREPL,'LREPL')
%individual_pl_DEMAND_files(MECPL,'MECPL')
%individual_pl_DEMAND_files(IBCTPL,'IBCTPL')

What is the change to the first version of the code that I need to make to have the resulting Excel file open without error loading table, so that I don't have to list out all the columns; which will change as the FY progresses.

Thanks

Re: ODS ExcelXP Tagset Error in Opening Resulting Excel File

Reeza — Sun, 25 Nov 2018 04:16:39 GMT

If you hard code it with proc prints does it work? If you hardcode it with proc report, but no style header, does it work?

You aren't showing the full code you ran. Can you please post the full code, using a code block to insert it please? For example, you have no FILE= statement so I know this isn't the full file. It's possible the error is in that section - in fact it's most likely there somewhere. That the log from two macro calls would be enough to see if there's any issues in your code.

Re: ODS ExcelXP Tagset Error in Opening Resulting Excel File

ErinKSimmons — Mon, 26 Nov 2018 02:12:02 GMT

I have attached the requested logs of both version (with the variables listed and with the variables not hardcoded). I have also attached the SAS dataset and the full code.

Note there are no errors, either version, within SAS. The only error comes on opening the Excel file when running the code without the variable explicitly listed out.

Re: ODS ExcelXP Tagset Error in Opening Resulting Excel File

Reeza — Mon, 26 Nov 2018 14:43:37 GMT

You're running an old version of the ODS Tagsets (2013). The latest version is 2015 and ODS Excel has replaced it as of 2017.

In your log: NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.130

You can find the newer version here:
https://support.sas.com/rnd/base/ods/odsmarkup/index.html

That may fix your issue.

topic ODS ExcelXP Tagset Error in Opening Resulting Excel File in Microsoft Integration with SAS

ODS ExcelXP Tagset Error in Opening Resulting Excel File

Re: ODS ExcelXP Tagset Error in Opening Resulting Excel File

Re: ODS ExcelXP Tagset Error in Opening Resulting Excel File

Re: ODS ExcelXP Tagset Error in Opening Resulting Excel File