Thanks for your response Vince. I tried using the provided code but then it displays the error message in excel (after an excel file gets downloaded) saying that " Excel cannot open the file 'Class.xlsx' because the file format or file extension is not valid. Verify that the file is not corrupted and that the file extension matches the format of the file". Also, I have attached the code that i am using to generate the output. data _null_; rc = appsrv_header('Content-type', 'application/vnd.ms-excel'); rc = appsrv_header('Content-disposition','attachment; filename="Class.xlsx"'); run; /* In this space are some Proc Sql Queries which derives the proc report data;*/ ods Excel file=_webout style=HTMLBlue;
ods excel options (sheet_name='Pivot Report');
%macro excel_PivotReport();
proc sql noprint;
select count(name) into :nTable from work.variable_names_aml;
quit;
%let ntable= %left(&ntable);
proc sql noprint;
select name into: va1 - :va&nTable. from work.variable_names_aml;
quit;
proc report nowd data=excel_pivot
Style (header)= [foreground=white background=teal];
columns &vlist.;
%do i=1 %to &nTable;
compute &&va&i.;
if &va1. = 'Grand Total' then do;
call define (_col_,'style','style={font_weight=bold background=white bordertopwidth=2px BORDERTOPSTYLE=double bordertopcolor=teal}');
end;
endcomp;
%end;
run;
%mend excel_PivotReport;
%excel_PivotReport ();
options missing=' ';
ods excel options (sheet_name='Sheet3');
proc report nowd data=sheet3_aml
Style (header)= [foreground=white background=teal];
columns SectorDesc TotCIF AvgTxnPerCIF TotAcct AvgTxnPerAcct TotTxns TotReversals TotCredit CRPctTot TotDebits DRPctTot;
compute SectorDesc;
if sectordesc = 'Grand Total' then do;
call define (_col_,'style','style={font_weight=bold background=white bordertopwidth=2px BORDERTOPSTYLE=double bordertopcolor=teal}');
end;
endcomp;
compute totcif;
if sectordesc = 'Grand Total' then do;
call define (_col_,'style','style={font_weight=bold background=white bordertopwidth=2px BORDERTOPSTYLE=double bordertopcolor=teal}');
end;
endcomp;
compute avgtxnpercif;
if sectordesc = 'Grand Total' then do;
call define (_col_,'style','style={font_weight=bold background=white bordertopwidth=2px BORDERTOPSTYLE=double bordertopcolor=teal}');
end;
endcomp;
compute totacct;
if sectordesc = 'Grand Total' then do;
call define (_col_,'style','style={font_weight=bold background=white bordertopwidth=2px BORDERTOPSTYLE=double bordertopcolor=teal}');
end;
endcomp;
compute avgtxnperacct;
if sectordesc = 'Grand Total' then do;
call define (_col_,'style','style={font_weight=bold background=white bordertopwidth=2px BORDERTOPSTYLE=double bordertopcolor=teal}');
end;
endcomp;
compute tottxns;
if sectordesc = 'Grand Total' then do;
call define (_col_,'style','style={font_weight=bold background=white bordertopwidth=2px BORDERTOPSTYLE=double bordertopcolor=teal}');
end;
endcomp;
compute totreversals;
if sectordesc = 'Grand Total' then do;
call define (_col_,'style','style={font_weight=bold background=white bordertopwidth=2px BORDERTOPSTYLE=double bordertopcolor=teal}');
end;
endcomp;
compute totcredit;
if sectordesc = 'Grand Total' then do;
call define (_col_,'style','style={font_weight=bold background=white bordertopwidth=2px BORDERTOPSTYLE=double bordertopcolor=teal}');
end;
endcomp;
compute crpcttot;
if sectordesc = 'Grand Total' then do;
call define (_col_,'style','style={font_weight=bold background=white bordertopwidth=2px BORDERTOPSTYLE=double bordertopcolor=teal}');
end;
endcomp;
compute totdebits;
if sectordesc = 'Grand Total' then do;
call define (_col_,'style','style={font_weight=bold background=white bordertopwidth=2px BORDERTOPSTYLE=double bordertopcolor=teal}');
end;
endcomp;
compute drpcttot;
if sectordesc = 'Grand Total' then do;
call define (_col_,'style','style={font_weight=bold background=white bordertopwidth=2px BORDERTOPSTYLE=double bordertopcolor=teal}');
end;
endcomp;
run;
... View more
