Hi Cynthia, Thanks for your reply. I try to use the COMPUTE and LINE, it works only on one report location. The dataset that need to be exported is imported from external file and have multiple merged rows. I want to export this dataset with the original format as possible. Original Code proc import datafile="C:\temp\test_pinnacle21-report.xlsx"
out=dataset_summary
dbms=xlsx
replace;
sheet="Dataset Summary";
getnames=no;
run;
%let out_file=C:\temp\test_%left(%sysfunc(datetime(), b8601dt.)).xlsx;
ods listing close;
ods excel file="&out_file" options(flow="tables" );
ods excel options(sheet_name= "Dataset Summary" absolute_row_height="20");
proc report data = dataset_summary nowd noheader missing
style(report)=[fontfamily="Arial" fontsize=10pt]
style(header)=[fontfamily="Arial" fontsize=11pt fontweight=light vjust=t]
style(column)=[fontfamily="Arial" fontsize=10pt];
column a--f;
define a / style(column)=[cellwidth=1in];
define b / style(column)=[cellwidth=4in];
define c / style(column)=[cellwidth=1.5in];
define d / style(column)=[cellwidth=1in];
define e / style(column)=[cellwidth=1in];
define f / style(column)=[cellwidth=1in];
compute a;
rownum+1;
if rownum eq 1 then call define(_col_,'style','style=[fontsize=14pt just=center vjust=top tagattr="mergeacross:6"]');
if not (index(upcase(a), "PROCESSED SOURCES") or index(upcase(a), "UNPROCESSED SOURCES")
or index(upcase(a),"DOMAIN") or index(upcase(a),"TOTAL") ) then do;
if rownum ge 5 and (mod(rownum, 2) gt 0) and not missing(a) then call define(_row_,'style','style=[background=#eaf1dd]');
end;
else if index(upcase(a), "PROCESSED SOURCES") or index(upcase(a), "UNPROCESSED SOURCES") then do;
call define(_col_,'style','style=[fontsize=11pt just=center vjust=top tagattr="mergeacross:6" background=#749938 color=white');
end;
else if index(upcase(a),"DOMAIN") then do;
call define(_row_,'style','style=[background=#c2d69a color=#749938');
end;
endcomp;
compute e;
if anyalpha(e) eq 0 and not missing(e) then call define(_col_,'style/merge','style=[just=r tagattr="type:Number"]');
endcomp;
compute f;
if anyalpha(f) eq 0 and f gt "0" then call define(_col_,'style','style=[background=red]');
if anyalpha(f) eq 0 and not missing(f) then call define(_col_,'style/merge','style=[just=r tagattr="type:Number"]');
if index(upcase(a),"TOTAL") then do;
call define("a",'style','style=[color=#749938]');
if not missing(e) then call define("e",'style','style=[background=#c2d69a color=#749938]');
end;
endcomp;
run;
ods excel close;
ods listing; ODS output So I wonder if there is a way to remove this grid line after merging cells or show grid line in the whole sheets like original files
... View more