proc report - merging headers (and columns) with across and grouped variables

[RJB_NZ]

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;

[ghosh]

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;

[RJB_NZ]

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.

[ghosh]

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

[RJB_NZ]

Unfortunately no, I'm not looking for alternativew ways to lay out the report.
Please go back to my original question to see the layout I need to recreate.

[RJB_NZ]

Though I'd be happy enough if a solution were to introduce more rows (perhaps merging across those.)

[RJB_NZ]

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;

[Athenkosi]

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;

[RJB_NZ · Accepted solution]

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;

[Daily1]

can i get this output on this format column first merge with cells