BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
HanaR
Fluorite | Level 6

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 Have CurrentlyWhat I WantWhat 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 */

 

1 ACCEPTED SOLUTION

Accepted Solutions
HanaR
Fluorite | Level 6

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

 

 

View solution in original post

7 REPLIES 7
supp
Pyrite | Level 9
I am having difficulty understanding what the desired change is. Do you want to change the row height for row 4?
HanaR
Fluorite | Level 6

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

ballardw
Super User

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.

HanaR
Fluorite | Level 6

Hello,

 

Thanks for suggestions.

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

 

Thanks again.

Sincerely,

Hana

Cynthia_sas
SAS Super FREQ

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):

results_excel_94M5.png

 

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;
 
HanaR
Fluorite | Level 6

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

HanaR
Fluorite | Level 6

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

 

 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 2620 views
  • 0 likes
  • 4 in conversation