Remove additional merged cell grid line or border created by ODS EXCEL with style tagattr

[enki37]

Hi, I use the following code to create excel that merge cells with style tagattr="mergeacross:3", but the output have additional grid line for merged row.

Could someone have an idea to remove this grid line? So the output format looks better. Thanks very much.

Code

data test;
    a="title";
    output;
    a="a";
    b="b";
    c="c";
    output;
run;

%let out_file=/test_%left(%sysfunc(datetime(), b8601dt.)).xlsx;

ods listing close;
ods excel file="&out_file" options(flow="tables" );
ods excel options(sheet_name= "test");

proc report data=test;
    column a b c;
    compute a;
        rownum+1;
        if rownum eq 1 then call define(_col_,'style','style=[ just=center vjust=top tagattr="mergeacross:3"]');
    endcomp;
run;

ods excel close;
ods listing;

Output 

[Cynthia_sas]

Hi, 

  I rarely need to stick my titles and extra lines into variables as you show. Usually, I just use the COMPUTE before if I need some line to appear between the column headers and the first row of data, like this:

Or, if I need for the line to appear ON TOP of the entire table, I do something like this:

With the COMPUTE BEFORE technique, the LINE text automatically spans the width of the report table without needing to use any suboptions.

Cynthia

[enki37]

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

[Cynthia_sas]

Hi:

  I think what you're seeing is a side effect of using mergeacross. I'm not sure that you need it. I can generate this with some fake data -- (sorry I don't open Excel files) -- and the columns beyond the last column do not show any lines.

  From looking at your code, it seems that your Excel sheet is already pre-summarized with spanning headers, and other text already supplied. It looks like you're parsing every line to get the color coding and other formatting the way you want. What I don't understand is if your importing the sheet from Excel and then exporting the sheet back to Excel, without doing any more calculations in SAS or data transformations what is SAS or ODS adding to the output? What is the purpose of the program?

Cynthia

  Here's the code I used:

data fakedata;
  length type domain label class source $20;
  infile datalines dsd dlm=',';
  input type $ roword Domain $ Label $ Class $ Source $ Records Rejects;
return;
datalines;
PS,1,Global,"Global Metadata",--,--,0,2
PS,2,AE,"Adverse Events",EVENTS,ae.xpt,257,0
PS,3,APAE,"Associated Persons Adverse Events",EVENTS,apae.xpt,0,0
PS,4,CE,"Clinical Events",EVENTS,ce.xpt,96,0
UPS,1,APAE,"Unknown","Unknown","Empty",.,0
;
run;

options nobyline missing=' ';
ods excel file='c:\temp\use_fakedata.xlsx'
    options(embedded_titles='yes' embedded_titles_once='yes' 
	        sheet_name='test' sheet_interval='none');
proc report data=fakedata
  style(header)={background=CXDBEDB9 color=black}
  style(summary)={background=white color=cx3d5218 font_weight=bold};
  title 'Pinnacle 21 Validator Report';
  column Type roword Domain Label Class Source Records Rejects;
  define type / order noprint page order=data;
  define roword / order noprint;
  define Domain / order 'Domain' ;
  define label / order 'Label' ;
  define class / 'Class';
  define source / 'Source';
  define records / sum;
  define rejects / sum
         style(header)={background=lightred color=black};
  break before type /page;
  break after type / summarize;
  compute before _page_/style=Header{background=cx85A647 color=CXDBEDB9};
    length brkline $30;
    if type = 'PS' then do;
	   brkline = 'Processed Sources: Canada';
	end;
	else if type = 'UPS' then do;
	   brkline = 'Unprocessed Sources: Germany';
	end;
	line brkline $30.;
  endcomp;
  compute roword;
    if _BREAK_ = ' ' then do;
       if mod(roword,2) ne 0 then do;
	     call define(_row_,'style','style={background=CXe5f2ce color=black}');
	   end;
	   else do;
	     call define(_row_,'style','style={background=white color=black}');
	   end;
    end;
  endcomp;
  compute after type;
     Domain = 'Total';
	 call define('rejects.sum','style','style={background=lightred}');
     line ' ';
  endcomp;
  rbreak after / summarize;
  compute after;
	 call define('rejects.sum','style','style={background=lightred}');
     Domain = 'Grand Total';
  endcomp;
run;
ods excel close;

[enki37]

Hi Cynthia,

For SAS, I am not sure that is a way to export new sheets in existing excel files on linux , so first I import all sheets in the original external file, then export to a new file in which some sheets are just kept original format, and others will have some manipulation like adding hyperlink to new datasets , and some new sheets are added to source file. 

I had tried to use ODS report writing interface to format excel , but it seems to be not easy. Until now the way used in my above code can create the new output file with the same format as original file approximately except this the unsolved side effect of using mergeacross.

Thanks a lot for your help.