Hi,
My code runs, yay! But doesn't get me the desired results...
I currently get:
But I'd like the top two header rows put into one.
And I'd like the last two of those column headers to be in red font and merged across 2 cells, to line up with the columns below them.
Like this:
I know that's a few things together, but I'm worried that they'll interact and so can't be considered separetly.
Thanks
Rodney
%let PathAndFile = 'c:\MyFolder\Shoes Test.xlsx';
data shoes_raw;
set sashelp.shoes;
where product in ('Sandal' 'Slipper' 'Sport Shoe');
run;
ODS excel
file = "&PathAndFile."
options ( embedded_titles='OFF' /*On means text in TITLE statement of proc tabulate will show in excel file*/
sheet_interval="none" /*when to split off to a new page - "none" means not at all*/
sheet_name='Shoes Tests' /*Name of Sheet*/
gridlines ='off' /*set printable gridlines*/
absolute_column_width='23, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10' /*setting column widths*/
absolute_row_height='15.75' /*setting column height*/
);
proc report data = shoes_raw missing;
*stating columns to be in the report and their order;
column region ('Products sold' stores,product)
('Sales amount' Sales,product)
('Sandal short' Sandal_Diff)
('Shoe short' Shoe_Diff)
;
*define columns directly from dataset variables;
define region / 'Global region' group
style(header)=[just=left borderbottomstyle=solid bordertopstyle=solid];
define stores / ' ' analysis sum format=comma.
style(header)=[just=right borderbottomstyle=solid bordertopstyle=solid tagattr='MERGEACROSS:2'];
define Sales / ' ' analysis sum format=dollar.2
style(header)=[just=right borderbottomstyle=solid bordertopstyle=solid tagattr='MERGEACROSS:2'];
define product / ' ' across order=internal
style(header)=[borderbottomstyle=solid bordertopstyle=solid just=right.];
*define cumputed columns;
define Sandal_Diff / "Slipper - Sandal" computed format=dollar.2
style(header)=[just=right borderbottomstyle=solid bordertopstyle=solid tagattr='MERGEACROSS:2']
style(column)=[ tagattr='MERGEACROSS:2'];
define Shoe_Diff / "Slipper - Sport Shoe" computed format=dollar.2
style(header)=[just=right borderbottomstyle=solid bordertopstyle=solid tagattr='MERGEACROSS:2']
style(column)=[tagattr='MERGEACROSS:2'];
*computing columns (that are based off other columns);
*columns produced from the across option of the 'month' variable do not have a name so must be reffered to by their column number, as can be counted in the column statement;
compute Sandal_Diff;
Sandal_Diff = _c6_ - _c5_;
endcomp;
compute Shoe_Diff;
Shoe_Diff = _c6_ - _c7_;
endcomp;
run;
ods excel close;
Thanks Athenkosi,
That's certainly covered the first step of getting all the heading on the correct lines.
I then realised I could do all my header cell merging using the report level header style with a traffic lighting version of tagattr = mergeaccross.
This did bring about a strange quirk, when the last header (Slipper - Sport shoe) was merged across cells it did the same to cell A1
That's why I added a Dummy Value label and coloured it white.
The code below carries on from Athenkosi code, and answers everything I need (it doesn't have the red colouring, but that'll be easy enough... fingers crossed)
Thanks everyone
ODS excel
file = "C:\My Folder\Shoes Test.xlsx"
options ( embedded_titles='OFF'
sheet_interval="none"
sheet_name='Shoes Tests'
gridlines ='off'
absolute_column_width='23, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10'
absolute_row_height='15.75'
);
proc format;
value $MergeAcross_Headers
'Sandal short' , 'Slipper - Sandal', 'Shoe short', 'Slipper - Sport shoe' = MERGEACROSS:2
;
value $DummyWhiteOut
'Dummy Value' = white
;
run;
proc report data=sashelp.shoes
style(header)=[tagattr=$MergeAcross_Headers. color=$DummyWhiteOut.];
where product in ('Sandal' 'Slipper' 'Sport Shoe');
column ('Dummy Value'(''('Global Regions' Region))) ('Products sold' Product),stores ('Sales amount' Product), sales
('Sandal short' ('' ('Slipper - Sandal' Sandal_Diff))) ('Shoe short' ('' ('Slipper - Sport shoe' Shoe_Diff)));
define Region / group order=internal;
define Product / across '';
define Region / '';
define stores / '';
define sales / '';
define Sandal_Diff / computed format=dollar.2 ''
style(column)=[ tagattr='MERGEACROSS:2'];
define Shoe_Diff / computed format=dollar.2 ''
style(column)=[ tagattr='MERGEACROSS:2'];
compute Shoe_Diff;
Shoe_Diff = _c6_ - _c7_;
Sandal_Diff = _c6_ - _c5_;
endcomp;
run;
ods excel close;
For the last two columns, I could not get a second set of labels (what you have shown in red). I guess you could do a combined label spanning the two columns.
proc report data = shoes_raw missing;
*stating columns to be in the report and their order;
column region ('Products sold' stores, product)
('Sales amount' Sales, product)
/*
('Sandal short' Sandal_Diff)
('Shoe short' Shoe_Diff)
*/
( Sandal_Diff Shoe_Diff)
;
*define columns directly from dataset variables;
define region / group 'Global region'
style(header)=[just=left borderbottomstyle=solid bordertopstyle=solid];
define stores / analysis sum format=comma. ''
style(header)=[just=right borderbottomstyle=solid bordertopstyle=solid tagattr='MERGEACROSS:2'];
define Sales / analysis sum format=dollar.2 ''
style(header)=[just=right borderbottomstyle=solid bordertopstyle=solid tagattr='MERGEACROSS:2'];
define product / across order=internal ''
style(header)=[borderbottomstyle=solid bordertopstyle=solid just=right.];
*define cumputed columns;
define Sandal_Diff / "Slipper - Sandal" computed format=dollar.2
style(header)=[just=right borderbottomstyle=solid bordertopstyle=solid tagattr='MERGEACROSS:2']
style(column)=[ tagattr='MERGEACROSS:2'];
define Shoe_Diff / "Slipper - Sport Shoe" computed format=dollar.2
style(header)=[just=right borderbottomstyle=solid bordertopstyle=solid tagattr='MERGEACROSS:2']
style(column)=[tagattr='MERGEACROSS:2'];
*computing columns (that are based off other columns);
*columns produced from the across option of the 'month' variable do not have a name so must be reffered to by their column number, as can be counted in the column statement;
compute Sandal_Diff;
Sandal_Diff = _c6_ - _c5_;
endcomp;
compute Shoe_Diff;
Shoe_Diff = _c6_ - _c7_;
endcomp;
run;
ods excel close;
Thanks ghosh,
I actually want individual titles for Sandal_Diff and Shoe_Diff (as per my original code).
I just want the whole of Sandal_Diff (data, and 2 levels of header) to span 2 Excel columns. And then likewise for Shoe_Diff.
The reason is that when using my real world (confidential) data then I need the header words to be across 2 columns to fit the words into the cells.
But I don't want to change the column width as that will muck up subsequent tables on the same sheet.
Frustratingly, this all looks fine in the SAS (HTML) output, it's just when I open the Excel spreadsheet that it doesn't work.
If you just need to fit in a long label just use the split option on the proc report line and embed the chosen split character in the label
Sorry I inserted the wrong code, and accepted it as the solution, doh!!!
This is the correct solution:
ODS excel
file = "C:\My Folder\Shoes Test.xlsx"
options ( embedded_titles='OFF'
sheet_interval="none"
sheet_name='Shoes Tests'
gridlines ='off'
absolute_column_width='23, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10'
absolute_row_height='15.75'
);
proc format;
value $MergeAcross_Headers
'Sandal short' , 'Slipper - Sandal', 'Shoe short', 'Slipper - Sport shoe' = MERGEACROSS:2
;
value $DummyWhiteOut
'Dummy Value' = white
;
run;
proc report data=sashelp.shoes
style(header)=[tagattr=$MergeAcross_Headers. color=$DummyWhiteOut.];
where product in ('Sandal' 'Slipper' 'Sport Shoe');
column ('Dummy Value'(''('Global Regions' Region))) ('Products sold' Product),stores ('Sales amount' Product), sales
('Sandal short' ('' ('Slipper - Sandal' Sandal_Diff))) ('Shoe short' ('' ('Slipper - Sport shoe' Shoe_Diff)));
define Region / group order=internal;
define Product / across '';
define Region / '';
define stores / '';
define sales / '';
define Sandal_Diff / computed format=dollar.2 ''
style(column)=[ tagattr='MERGEACROSS:2'];
define Shoe_Diff / computed format=dollar.2 ''
style(column)=[ tagattr='MERGEACROSS:2'];
compute Shoe_Diff;
Shoe_Diff = _c6_ - _c7_;
Sandal_Diff = _c6_ - _c5_;
endcomp;
run;
ods excel close;
Try this.
ODS excel
file = "c:\MyFolder\Shoes Test.xlsx"
options ( embedded_titles='OFF'
sheet_interval="none"
sheet_name='Shoes Tests'
gridlines ='off'
absolute_column_width='23, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10'
absolute_row_height='15.75'
);
proc report data=sashelp.shoes;
where product in ('Sandal' 'Slipper' 'Sport Shoe');
column ('Global Regions' Region) ('Products sold' Product),stores ('Sales amount' Product), sales
('Sandal short' ('' ('Slipper - Sandal' Sandal_Diff))) ('Shoe short' ('' ('Slipper - Sport shoe' Shoe_Diff)));
define Region / group order=internal;
define Product / across '';
define Region / '';
define stores / '';
define sales / '';
define Sandal_Diff / computed format=dollar.2 '';
define Shoe_Diff / computed format=dollar.2 '';
compute Shoe_Diff;
Shoe_Diff = _c6_ - _c7_;
Sandal_Diff = _c6_ - _c5_;
endcomp;
run;
ods excel close;
Thanks Athenkosi,
That's certainly covered the first step of getting all the heading on the correct lines.
I then realised I could do all my header cell merging using the report level header style with a traffic lighting version of tagattr = mergeaccross.
This did bring about a strange quirk, when the last header (Slipper - Sport shoe) was merged across cells it did the same to cell A1
That's why I added a Dummy Value label and coloured it white.
The code below carries on from Athenkosi code, and answers everything I need (it doesn't have the red colouring, but that'll be easy enough... fingers crossed)
Thanks everyone
ODS excel
file = "C:\My Folder\Shoes Test.xlsx"
options ( embedded_titles='OFF'
sheet_interval="none"
sheet_name='Shoes Tests'
gridlines ='off'
absolute_column_width='23, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10'
absolute_row_height='15.75'
);
proc format;
value $MergeAcross_Headers
'Sandal short' , 'Slipper - Sandal', 'Shoe short', 'Slipper - Sport shoe' = MERGEACROSS:2
;
value $DummyWhiteOut
'Dummy Value' = white
;
run;
proc report data=sashelp.shoes
style(header)=[tagattr=$MergeAcross_Headers. color=$DummyWhiteOut.];
where product in ('Sandal' 'Slipper' 'Sport Shoe');
column ('Dummy Value'(''('Global Regions' Region))) ('Products sold' Product),stores ('Sales amount' Product), sales
('Sandal short' ('' ('Slipper - Sandal' Sandal_Diff))) ('Shoe short' ('' ('Slipper - Sport shoe' Shoe_Diff)));
define Region / group order=internal;
define Product / across '';
define Region / '';
define stores / '';
define sales / '';
define Sandal_Diff / computed format=dollar.2 ''
style(column)=[ tagattr='MERGEACROSS:2'];
define Shoe_Diff / computed format=dollar.2 ''
style(column)=[ tagattr='MERGEACROSS:2'];
compute Shoe_Diff;
Shoe_Diff = _c6_ - _c7_;
Sandal_Diff = _c6_ - _c5_;
endcomp;
run;
ods excel close;
can i get this output on this format column first merge with cells
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.