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 🙂
... View more