topic Re: Selective bolding and indenting using PROC REPORT and ODS EXCELXP tagsets in ODS and Base Reporting

Selective bolding and indenting using PROC REPORT and ODS EXCELXP tagsets

ginak — Mon, 10 Jul 2017 20:51:03 GMT

Hello,

I want to bold certain rows that I'm printing out using proc report and ods EXCELXP tagset, and then indent the rows that don't get bolded. How can I do this? It's not showing up in my output 😞 The indented rows show up as indented in the dataset (see attached picture). When I use proc report they also look indented (see attached picture). However, when I use ods tagsets to get the report to excel, the rows are not indented (see attached picture).

Here is some sample code:

PROC REPORT DATA = tempdata;
	column classval0 ORCL_M1 p_M1 ORCL_M2 p_M2 ORCL_M3 p_M3 ORCL_M4 p_M4 ORCL_M5 p_M5;
        
        /*align the p-values by decimal places, also highlight the significant ones*/
	define  ORCL_M1  / display center style(column) = [background=sigf.];
	define  p_M1     / display center style(column) = [background=sigf. just = d];
	 

 	define  classval0 / DISPLAY style=[asis=on];
	/*We want to bold the following words in classval0*/
	compute classval0;
		if classval0 in ("Intercept", 
					"Race (REF = White)"										,
					"Female"													,
					"LGBT (REF = Heterosexual"									,
					"Marital status (REF = Married)"							,
					"At least 1 child <18"										,
					"Never deployed (REF = Ever deployed)"						,
					"Combat zone deployments (REF = 3+ deployments)"			,
					"Length of deployment (REF = No deployment in past 12 mo)"	, 
					"Service Branch (REF = USCG)"								,
					"Pay Grade (REF = O1-O10)"									,
					"Age Group (REF = 25-34)")									
		then call define ('_c1_', 
					"style", 
					"style = [font_weight=bold]");

		/*I want to indent the words that we did not bold in classval0*/
			else call define ('_c1_', "style", "style = [rightIndent]");
		 endcomp;
 run;

I found some useful sugi reports that other users posted in similar questions to this (that's how I thought to use the "rightindent" statement, even though I dont' think I used ti correctly), as well as the justify=d and font_weight=bold option. Something is off about mine though 😞

in my macro to create an excel sheet, i use the styles.xlstatistical option.

Any advice and help would be greatly appreciated. Thank you!

PS - I've tried to post this question 3 times and it got rejected so I'm hoping this is useful.

Re: Selective bolding and indenting using PROC REPORT and ODS EXCELXP tagsets

Cynthia_sas — Mon, 10 Jul 2017 22:45:46 GMT

Hi:

Did you check the documentation on INDENT? I am not sure that all destinations support indenting. RTF and PDF are most reliable, but I would not expect TAGSETS.EXCELXP or ODS EXCEL or even ODS HTML to support INDENT. You did not show your ODS statements. When I ran this quick test, only RTF and PDF were indented:

ods html file='c:\temp\tryindent.html';
ods rtf file='c:\temp\tryindent.rtf';
ods pdf file='c:\temp\tryindent.pdf';
ods excel file='c:\temp\tryindent_xl.xlsx';
ods tagsets.excelxp file='c:\temp\tryindent_xp.xml' style=htmlblue;

proc report data=sashelp.class;
  where name in ('Alfred', 'Barbara','John', 'Robert');
  column name age height weight sex;
  define name / order style(column)={width=2in};
  compute name;
    if name in ('Alfred', 'John') then 
	   call define(_col_,'style','style={font_size=11pt font_weight=bold}');
	else call define(_col_,'style','style={leftmargin=.25in font_weight=medium}');
  endcomp;
run;

ods _all_ close;

I suspect when you check the doc, you will find that however you created and are using "rightIndent" -- that it may not be supported in ODS TAGSETS.EXCELXP or ODS EXCEL.

cynthia

Re: Selective bolding and indenting using PROC REPORT and ODS EXCELXP tagsets

ginak — Tue, 11 Jul 2017 00:22:28 GMT

Hi Cynthia,

Thanks for your suggestions! AFter reading your suggestion, I did some research. I guess I did not properly define my rightIndent and assumed it was a sas option. I did some research and found that I need to define it in a proc template, as was done in this paper:

http://www.lexjansen.com/nesug/nesug09/np/NP03.pdf in the compute c block at the bottom of page 8. They explain it a bit at the top of page 9. They said earlier in the document that

"Both of these SAS datasets include character variables that are indented to the right (PATCHAR for Table 1 and EFFECT_NAME for Table 2). These indentations were created simply by concatenating the string 'A0A0A0A0'x at the beginning of the character variable.

patchar = catx('','A0A0A0A0'x, (put(&rowvar,&&&rowvar.fmt));

I'll try this out tomorrow and see if it works!

Re: Selective bolding and indenting using PROC REPORT and ODS EXCELXP tagsets

Cynthia_sas — Tue, 11 Jul 2017 00:46:59 GMT

Ah, that may work because they are padding the character string on the left with unprintable hex characters.

In my experience, most of these demographic reports are usually done in RTF and PDF, where it is far easier to use the simple LEFTMARGIN= to perform the indenting.

cynthia