topic Re: ODS EXCEL PROC REPORT - Spanning Header Border in New SAS User

ODS EXCEL PROC REPORT - Spanning Header Border

HanaR — Thu, 21 Nov 2019 20:19:28 GMT

Hello All,

I am Hana.

I am running SAS 9.4 M2 on Linux Server.

Currently, having trouble with spanning header.

I included my code and outputs- What I have and What I want.

If you could help with this problem, I'd really appreciate it.

Thanks.

Hana

options formchar="|----|+|---+=|-/\<>*";
What I Have CurrentlyWhat I Want
%macro generate_excel_tables(run_env=);

 %let excel_file_name = %str(ods_excel_ouput_&run_env..xlsx);

 %if &run_env. = %str(windows) %then %do;
     %let excel_file_out = %str(C:\Users\Reed_H\Downloads\TEMP\&excel_file_name.); /* windows */
 %end;
 %else %do;
     %let excel_file_out = %str(/home/reedh/reedTest/data/output/&excel_file_name.); /* linux */
 %end;
  
  ods listing close;
  
  ods escapechar='^';
  
  options leftmargin=0.35in
          rightmargin=0.35in
          topmargin=0.75in
          bottommargin=0.75in
          papersize=letter
          ;
  
  ods excel file = "&excel_file_out."
            options (embedded_titles='yes'
                     embedded_footnotes='yes'
                     orientation='landscape'
                     print_header_margin='0.3'
                     print_footer_margin='0.3'
                     print_footer="Page &P of &N"
                    ) 
            ; 
                 
  title font='Times New Roman' justify=left h=11pt color=black "****** ODS EXCEL ******** ";
         
  ods excel options(row_repeat='1-6'
                    scale="80");

  proc report data=sashelp.citiqtr(obs=1) nowd split = '*'
              style(report)={rules = all
                             frame = box}

              style(header)={font_face = "times new roman"
                             font_size = 10pt
                             font_weight = medium
                             font_style = roman
                             foreground = black
                             background = white
                             just = c
                             vjust = c
                             bordertopcolor = black
                             borderbottomcolor = black
                             borderleftcolor = black
                             borderrightcolor = black
                             bordertopwidth=5
                             borderbottomwidth=10}

              style(column)={font_face = "Albany AMT"
                             font_size = 10pt
                             font_weight = medium
                             font_style = roman
                             foreground = black
                             background = white
                             bordertopcolor = black
                             borderbottomcolor = black
                             borderleftcolor = black
                             borderrightcolor = black}
              ;
              columns bpb
                      bpcr
                     ("^S={borderleftcolor=red borderrightcolor=red borderleftwidth=10 borderrightwidth=10}spanning header11"
                       ("^S={borderleftcolor=red borderrightcolor=red borderleftwidth=10 borderrightwidth=10}spanning header21" gc gd)
                       ("^S={borderleftcolor=red borderrightcolor=red borderleftwidth=10 borderrightwidth=10}spanning header31" gcdq gcq))                        
                     ("^S={borderleftcolor=red borderrightcolor=red borderleftwidth=10 borderrightwidth=10}spanning header12"
                       ("^S={borderleftcolor=red borderrightcolor=red borderleftwidth=10 borderrightwidth=10}spanning header22" gdp gyd)
                       ("^S={borderleftcolor=red borderrightcolor=red borderleftwidth=10 borderrightwidth=10}spanning header42" gdpq gydq))
              ;               
              define bpb  / "Bal1"; 
              define bpcr / "Bal2"  ; 
              define gc   / "GC" 
                             style(header column)={borderleftwidth=10}; 
              define gd   / "GD" ; 
              define gcdq / "GCDQ" ; 
              define gcq  / "GCQ" ; 
              define gdp  / "GDP" 
                             style(header column)={borderleftwidth=10};
              define gyd  / "GYD" ; 
              define gdpq / "GDPQ" ; 
              define gydq / "GYDQ" ; 
  run;
 
  ods excel close;
  ods listing;
 
%exit: %mend generate_excel_tables;

%generate_excel_tables(run_env=linux) /* windows or linux */

Re: ODS EXCEL PROC REPORT - Spanning Header Border

supp — Thu, 21 Nov 2019 21:08:52 GMT

I am having difficulty understanding what the desired change is. Do you want to change the row height for row 4?

Re: ODS EXCEL PROC REPORT - Spanning Header Border

HanaR — Thu, 21 Nov 2019 21:15:59 GMT

Hello,

What I want to get is to have "THICK LEFT Border" for "spanning header11", "spanning header12, "spanning header21", "spanning header22".

When I ran the code on Windows, I can see it, but somehow it is not working on Linux environment.

I attached the excel file.

Thanks.

Hana

Re: ODS EXCEL PROC REPORT - Spanning Header Border

ballardw — Thu, 21 Nov 2019 21:28:24 GMT

I would suggest providing an actual unit in any width setting, or anything related to size actually. Some ranges of values depending on ODS destination/operating system / viewer seem to get treated as narrow/ medium / wide and small differences may not be visible.

You can use fractional measurements such as 1.5mm or 0.1in.

Pixels however are not a good unit as they depend on the display device and are not consistent.

Re: ODS EXCEL PROC REPORT - Spanning Header Border

HanaR — Thu, 21 Nov 2019 21:51:24 GMT

Hello,

Thanks for suggestions.

Even specifying measurement, "inch", still doesn't display thicker border for those spanning header.

Thanks again.

Sincerely,

Hana

Re: ODS EXCEL PROC REPORT - Spanning Header Border

Cynthia_sas — Thu, 21 Nov 2019 23:19:31 GMT

Hi:

I could not zoom on your picture to see the 2 screen shots larger. This is what I get in SAS 9.4 M5 when I run your code (slightly modified):

Here's the code I ran. I did not see the need for your macro, since I am running on Windows:


   
  ods listing close;
  
  ods escapechar='^';
  
  options leftmargin=0.35in
          rightmargin=0.35in
          topmargin=0.75in
          bottommargin=0.75in
          papersize=letter
          ;
  
  ods excel file = "c:\temp\style_excel.xlsx"
            options (embedded_titles='yes'
                     embedded_footnotes='yes'
                     orientation='landscape'
                     print_header_margin='0.3'
                     print_footer_margin='0.3'
                     print_footer="Page &P of &N"
                    ) 
            ; 
                 
  title font='Times New Roman' justify=left h=11pt color=black 
      "****** ODS EXCEL version &sysvlong4 ******** ";
         
  ods excel options(row_repeat='1-6'
                    scale="80");

  proc report data=sashelp.citiqtr(obs=1) nowd split = '*'
              style(report)={rules = all
                             frame = box}

              style(header)={font_face = "times new roman"
                             font_size = 10pt
                             font_weight = medium
                             font_style = roman
                             foreground = black
                             background = white
                             just = c
                             vjust = c
                             bordertopcolor = black
                             borderbottomcolor = black
                             borderleftcolor = black
                             borderrightcolor = black
                             bordertopwidth=5
                             borderbottomwidth=10}

              style(column)={font_face = "Albany AMT"
                             font_size = 10pt
                             font_weight = medium
                             font_style = roman
                             foreground = black
                             background = white
                             bordertopcolor = black
                             borderbottomcolor = black
                             borderleftcolor = black
                             borderrightcolor = black}
              ;
              columns bpb
                      bpcr
                     ("^S={borderleftcolor=red borderrightcolor=red borderleftwidth=10 borderrightwidth=10}spanning header11"
                       ("^S={borderleftcolor=red borderrightcolor=red borderleftwidth=10 borderrightwidth=10}spanning header21" gc gd)
                       ("^S={borderleftcolor=red borderrightcolor=red borderleftwidth=10 borderrightwidth=10}spanning header31" gcdq gcq))                        
                     ("^S={borderleftcolor=red borderrightcolor=red borderleftwidth=10 borderrightwidth=10}spanning header12"
                       ("^S={borderleftcolor=red borderrightcolor=red borderleftwidth=10 borderrightwidth=10}spanning header22" gdp gyd)
                       ("^S={borderleftcolor=red borderrightcolor=red borderleftwidth=10 borderrightwidth=10}spanning header42" gdpq gydq))
              ;               
              define bpb  / "Bal1"; 
              define bpcr / "Bal2"  ; 
              define gc   / "GC" 
                             style(header column)={borderleftwidth=10}; 
              define gd   / "GD" ; 
              define gcdq / "GCDQ" ; 
              define gcq  / "GCQ" ; 
              define gdp  / "GDP" 
                             style(header column)={borderleftwidth=10};
              define gyd  / "GYD" ; 
              define gdpq / "GDPQ" ; 
              define gydq / "GYDQ" ; 
  run;
 
  ods excel close;

Re: ODS EXCEL PROC REPORT - Spanning Header Border

HanaR — Fri, 22 Nov 2019 13:40:26 GMT

Hello,

I didn't have any problem to get the results when I ran this code on windows, SAS9.4M5.

The problem is that the code ran on SAS 9.4 M2 on Linux Server.

Thanks.

Hana

Re: ODS EXCEL PROC REPORT - Spanning Header Border

HanaR — Fri, 22 Nov 2019 13:46:11 GMT

Hello All,

I got an answer from SAS tech Support; it is related to SAS version.

This issue wouldn't appear sas 9.4 m3 and higher version.

Many thanks to all who responded to this issue.

Sincerely,

Hana