Hello I'm looking to put vertical borders between the different parts of my (non-trivial) table. I almost got there...
I'm getting this (notice the gaps in the lines at cells D2 and G2):
I'd like to have this:
%let TempPath = %sysfunc(getoption(work)); * ref to Work library;
%put &=TempPath;
ODS excel
file = "&TempPath./Shoes Test.xlsx"
style= Listing
options ( embedded_titles='OFF'
sheet_interval="none"
sheet_name='Shoes Tests'
gridlines ='off'
absolute_column_width='23, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12'
absolute_row_height='15.75'
);
data altered_shoes;
set sashelp.shoes;
where product in ('Sandal' 'Slipper' 'Sport Shoe');
if product = 'Sandal' then month = '31Jan2020'd;
if product = 'Slipper' then month = '31Dec2020'd;
if product = 'Sport Shoe' then month = '31Jan2021'd;
run;
proc format;
value $MergeAcross_Headers
'Month Diff', 'Jan 21 - Dec 20', 'Year Diff', 'Jan 21 - Jan 20' = MERGEACROSS:2
;
value $DummyWhiteOut
'Dummy Value' = lightgrey
;
value $tester
'Jan 21'd = solid
;
value $header_rborder
'Products sold', 'Sales amount' = solid
;
run;
proc report data=altered_shoes
style(header)=[tagattr=$MergeAcross_Headers. color=$DummyWhiteOut. borderrightstyle=$header_rborder.];
format month Mon_YY. ;
column ('Dummy Value'(''('Global Regions' Region)))
('Products sold' month),stores
('Sales amount' month), sales
('Month Diff' ('' ('Jan 21 - Dec 20' Month_Diff)))
('Year Diff' ('' ('Jan 21 - Jan 20' Year_Diff)))
boarder_formater;
define Region / '' group order=internal;
define month / '' across order=internal
style(header)=[borderrightstyle=$tester.]; *<<<< Not working :-( ;
define stores / '';
define sales / '';
define Month_Diff / computed format=dollar.2 ''
style(column)=[ tagattr='MERGEACROSS:2'];
define Year_Diff / computed format=dollar.2 ''
style(column)=[ tagattr='MERGEACROSS:2'];
define boarder_formater / noprint;
compute Year_Diff;
Month_Diff = _c5_ - _c7_;
Year_Diff = _c6_ - _c7_;
endcomp;
compute boarder_formater;
call define(4, "style/merge", "style=[borderrightstyle=solid]");
call define(7, "style/merge", "style=[borderrightstyle=solid]");
endcomp;
run;
ods excel close;
Thanks
Rodney
Andy from SAS Technical Support found an answer for this: adding 'JAN21' to the $header_rborder format.
Yes a character value format for a date (numeric) field that been turned into a header...
%let TempPath = %sysfunc(getoption(work)); * ref to Work library;
%put &=TempPath;
data altered_shoes;
set sashelp.shoes;
where product in ('Sandal' 'Slipper' 'Sport Shoe');
if product = 'Sandal' then month = '31Jan2020'd;
if product = 'Slipper' then month = '31Dec2020'd;
if product = 'Sport Shoe' then month = '31Jan2021'd;
run;
proc format;
value $MergeAcross_Headers
'Month Diff', 'Jan 21 - Dec 20', 'Year Diff', 'Jan 21 - Jan 20' = MERGEACROSS:2
;
value $DummyWhiteOut
'Dummy Value' = lightgray
;
value $header_rborder
'Products sold', 'Sales amount', 'JAN21' = solid
;
run;
ODS excel
file = "&TempPath./Shoes Test.xlsx"
style= Listing
options ( embedded_titles='OFF'
sheet_interval="none"
sheet_name='Shoes Tests'
gridlines ='off'
absolute_column_width='23, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12'
absolute_row_height='15.75'
);
proc report data=altered_shoes
style(header)=[tagattr=$MergeAcross_Headers. color=$DummyWhiteOut. borderrightstyle=$header_rborder.];
format month MonYY. ;
column ('Dummy Value'(''('Global Regions' Region)))
('Products sold' month),stores
('Sales amount' month), sales
('Month Diff' ('' ('Jan 21 - Dec 20' Month_Diff)))
('Year Diff' ('' ('Jan 21 - Jan 20' Year_Diff)))
boarder_formater;
define Region / '' group order=internal;
define month / '' across order=internal;
define stores / '';
define sales / '';
define Month_Diff / computed format=dollar.2 ''
style(column)=[ tagattr='MERGEACROSS:2'];
define Year_Diff / computed format=dollar.2 ''
style(column)=[ tagattr='MERGEACROSS:2'];
define boarder_formater / noprint;
compute Year_Diff;
Month_Diff = _c5_ - _c7_;
Year_Diff = _c6_ - _c7_;
endcomp;
compute boarder_formater;
call define(4, "style/merge", "style=[borderrightstyle=solid]");
call define(7, "style/merge", "style=[borderrightstyle=solid]");
endcomp;
run;
ods excel close;
Now I just need to calculate the 'JAN21' label and put it into a macro variable so that my code survives month to month 🙂
@RJB_NZ wrote:
PS the $tester. method worked when I had a character variable as my across group, but I haven't been able to get it to work with a date variable.
Conflicting value/format. Your MONTH variable is a DATE, which is numeric from the format you apply. The format you attempt to create $tester would be for CHARACTER values. Your attempt to use "Jan 21"d as a "date" fails for two reasons. First literal dates must be in the form of 'ddMONyy'd or 'ddMONyyyy'd, the day of the month MUST be present in a literal date.
Second you are using format on a date to create a group, so you don't know which underlying value of the variable may actually be there.
Try creating a format like
value tester '01JAN2021'd - '31JAN2021'd = 'solid' ;
Andy from SAS Technical Support found an answer for this: adding 'JAN21' to the $header_rborder format.
Yes a character value format for a date (numeric) field that been turned into a header...
%let TempPath = %sysfunc(getoption(work)); * ref to Work library;
%put &=TempPath;
data altered_shoes;
set sashelp.shoes;
where product in ('Sandal' 'Slipper' 'Sport Shoe');
if product = 'Sandal' then month = '31Jan2020'd;
if product = 'Slipper' then month = '31Dec2020'd;
if product = 'Sport Shoe' then month = '31Jan2021'd;
run;
proc format;
value $MergeAcross_Headers
'Month Diff', 'Jan 21 - Dec 20', 'Year Diff', 'Jan 21 - Jan 20' = MERGEACROSS:2
;
value $DummyWhiteOut
'Dummy Value' = lightgray
;
value $header_rborder
'Products sold', 'Sales amount', 'JAN21' = solid
;
run;
ODS excel
file = "&TempPath./Shoes Test.xlsx"
style= Listing
options ( embedded_titles='OFF'
sheet_interval="none"
sheet_name='Shoes Tests'
gridlines ='off'
absolute_column_width='23, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12'
absolute_row_height='15.75'
);
proc report data=altered_shoes
style(header)=[tagattr=$MergeAcross_Headers. color=$DummyWhiteOut. borderrightstyle=$header_rborder.];
format month MonYY. ;
column ('Dummy Value'(''('Global Regions' Region)))
('Products sold' month),stores
('Sales amount' month), sales
('Month Diff' ('' ('Jan 21 - Dec 20' Month_Diff)))
('Year Diff' ('' ('Jan 21 - Jan 20' Year_Diff)))
boarder_formater;
define Region / '' group order=internal;
define month / '' across order=internal;
define stores / '';
define sales / '';
define Month_Diff / computed format=dollar.2 ''
style(column)=[ tagattr='MERGEACROSS:2'];
define Year_Diff / computed format=dollar.2 ''
style(column)=[ tagattr='MERGEACROSS:2'];
define boarder_formater / noprint;
compute Year_Diff;
Month_Diff = _c5_ - _c7_;
Year_Diff = _c6_ - _c7_;
endcomp;
compute boarder_formater;
call define(4, "style/merge", "style=[borderrightstyle=solid]");
call define(7, "style/merge", "style=[borderrightstyle=solid]");
endcomp;
run;
ods excel close;
Now I just need to calculate the 'JAN21' label and put it into a macro variable so that my code survives month to month 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.