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:
%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;
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')
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!