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="|----|+|---+=|-/\<>*"; %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 */
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
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
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.
Hello,
Thanks for suggestions.
Even specifying measurement, "inch", still doesn't display thicker border for those spanning header.
Thanks again.
Sincerely,
Hana
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;
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
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
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!
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.