topic Re: Proc report vertical border for headers between two across groups (ods excel) in SAS Programming

Proc report vertical border for headers between two across groups (ods excel)

RJB_NZ — Tue, 16 Mar 2021 02:52:00 GMT

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

Re: Proc report vertical border for headers between two across groups (ods excel)

RJB_NZ — Tue, 16 Mar 2021 02:55:25 GMT

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.

Re: Proc report vertical border for headers between two across groups (ods excel)

ballardw — Tue, 16 Mar 2021 14:46:08 GMT

@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'
;

Re: Proc report vertical border for headers between two across groups (ods excel)

RJB_NZ — Tue, 16 Mar 2021 20:55:14 GMT

Thanks @ballardw
That makes a lot of sense, unfortunately it doesn't work for me.
Did it work for you when you tested it inside my code?

Re: Proc report vertical border for headers between two across groups (ods excel)

ballardw — Tue, 16 Mar 2021 20:59:50 GMT

@RJB_NZ wrote:
Thanks @ballardw
That makes a lot of sense, unfortunately it doesn't work for me.
Did it work for you when you tested it inside my code?

Can't test code without data and I'm way too lazy to try to dummy something up that would use all the Excel options plus the report code.

Re: Proc report vertical border for headers between two across groups (ods excel)

RJB_NZ — Tue, 16 Mar 2021 21:04:42 GMT

The data step in my code creates the data for you.

The only thing you have to do to run my code is if your server is Windows based then change the / to a \ within the statement <<< ODS excel file = "&TempPath./Shoes Test.xlsx" >>>

Re: Proc report vertical border for headers between two across groups (ods excel)

RJB_NZ — Tue, 16 Mar 2021 21:09:32 GMT

Sorry, that's assuming that you have access to sashelp.shoes

Re: Proc report vertical border for headers between two across groups (ods excel)

RJB_NZ — Tue, 30 Mar 2021 01:17:42 GMT

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 🙂