Conditionally formatting rows in a rtf file based on the values of a column with COMPUTE block

Chang — Fri, 16 Aug 2019 02:28:48 GMT

Hi,

I am generating a RTF report with 3 tables. I would like to put rows in bold-face if values in a p value column are lower than a threshold. What I have done is basically to duplicate the p value column, place it as the first variable in a SAS data set, and use it in a COMPUTE block. I ended up having to use the .sum keyword inconsistently in these 3 tables in order to correctly format my rows (i.e. only bold rows when p values are < a threshold). I haven't been able to figure out why such inconsistency. But I think the COMPUTE blocks in these 3 tables may explain why. I am giving a summary of my .sum usage in the three tables. I am hoping someone (Cynthia?) can show me the code that will result in consistent output.

Table	syntax form of variable to evaluate	Result
Table S1	dup_p_value.sum	Table rows correctly highlighted
Table S1	dup_p_value	every Table row in boldface
Table S3	dup_rG_p_value.sum	Sometimes Table rows correctly highlighted; sometimes an error returned
Table S3	dup_rG_p_value	every Table row in boldface
Table S4	pval_dup	Table rows correctly highlighted
Table S4	pval_dup.sum	an error returned and blank table body

I am copying the COMPUTE block code and a printscreen of a table (Table S1, Table S3, and Table S4). I will copy my full working SAS code at the end as it is lengthy.

Table S1. Here is the SAS cod and output of Table S1 which rows with p values < a threshold are displayed correctly in bold-face

compute SE; /*compute predictor/character;*/
	if dup_p_value.sum < 0.0125 then	call define(_row_, 'style', 'style=[font_weight=bold]' );
endcomp;

Table S1. Actually the use of .sum keyword is unnecessary, because the purpose of dup_p_value is for display, not summation. However, using dup_p_value in the COMPUTE block resulted in all the rows in boldface

compute SE; /*compute predictor/character;*/
	if dup_p_value < 0.0125 then	call define(_row_, 'style', 'style=[font_weight=bold]' );
endcomp;

Table S3. Using .sum in the COMPUTE block yesterday returned an error: ERROR: The variable type of DUP_RG_P_VALUE.SUM is invalid in this context. But it is working now.

compute rG_esti;
	if dup_rG_p_value.sum < 0.001111111 then 
		do;
			call define(_row_, 'style', 'style=[font_weight=bold]' );
		end;
endcomp;

Table S3. After the removal of .sum, every row is in bold-face, even for those p values > the threshold.

compute rG_esti;
	if dup_rG_p_value < 0.001111111 then 
		do;
			call define(_row_, 'style', 'style=[font_weight=bold]' );
		end;
endcomp;

Table S4. This table is correctly formatted without the use of .sum

compute method_abbreviated/character ;
	if pval_dup < 0.0002777778 then do;
		call define(_row_, 'style', 'style=[font_weight=bold]' );
	end;
endcomp;

Table S4. Using .sum keyword returns the error: "ERROR: The variable type of PVAL_DUP.SUM is invalid in this context.
"

compute method_abbreviated/character ;
	if pval_dup.sum < 0.0002777778 then do;
		call define(_row_, 'style', 'style=[font_weight=bold]' );
	end;
endcomp;

and results in blank table

My full code for Table S1 (working)

proc report	data=tem._NU_manuscript04_table&table_order.
					contents=""  /*remove default 2nd node*/
					nowd 
					split='|'
					style(report)={width=100%  
								   cellpadding=8px
								   font_face='Times New Roman' 
								   font_size=10pt 
								   borderwidth=1.5pt /* border thickness*/ 
								   background = TRANSPARENT}
				    style(header)={just=center 
								   font_weight=bold 
								   font_face='Times New Roman' 
								   font_size=10pt 
								   font_style=roman 
								   protectspecialchars = OFF 
								   background = TRANSPARENT}
					 style(column)={font_face='Times New Roman' 
									font_size=10pt 
									background = TRANSPARENT}
     ;

/*The COLUMN statement is used to list each report column*/
column OBSNO dup_p_value iteration model dep_var_label predictor SE p_value effect_size_95CI
			; 

/*Each column, in turn, has a DEFINE statement that describes how that column is created and formatted.*/

/*Every value of these two variables was displayed rather than just the first occurrence*/
%def_group(	cVar=iteration
						,option=group	
						,cName=Model no. 
						,isFmt=N					
						,cFmt=						
						,cWide=1.5 cm
						,headerAlign=left 
						,colAlign=left	);

%def_group(	cVar=model		
						,option=group	
						,cName=Method 
						,isFmt=N				
						,cFmt=					
						,cWide=2.5 cm
						,headerAlign=left 
						,colAlign=left	);

DEFINE OBSNO/ ORDER NOPRINT	;
/*Set conditional variables to noprint*/
define dup_p_value /noprint;

%def_display(	cVar=dep_var_label,		cName=Outcome,isFmt=N,cFmt=,cWide=2 cm,	headerAlign= left, colAlign= left); /*cFmt=$var_licit_substance_use_abb.*/
%def_display(	cVar=predictor,		cName=Predictor,isFmt=N,cFmt= ,cWide=4 cm,	headerAlign= left, colAlign= left); 

*%def_display(	cVar=sample_size,	cName=Sample size,			 	cWide=1.5 cm,	headerAlign= right, colAlign= right); 
%def_display(	cVar=SE,	cName=SE,isFmt=Y,	cFmt=5.3,	cWide=1.5 cm,	headerAlign= right, colAlign= right); 
%def_display(	cVar=p_value,	cName=P value,isFmt=N,	cFmt=,	 cWide=1.5 cm,	headerAlign=right, colAlign=right);
%def_display(	cVar=effect_size_95CI,	cName=Effect size (95% CI),isFmt=N,	cFmt=,	 cWide=2.25 cm,	headerAlign=right, colAlign=right);

/*Bold rows if p values survive multiple testing 0.0125 (0.05/4)
condition variable dup_p_value must be on the left of the COMPUTE block variable predictor 
call define(_col_) applies the format on the compute variable column-wide
call define(_row_) applies the format across entire row*/

compute SE; /*compute predictor/character;*/
	if dup_p_value.sum < 0.0125 then	call define(_row_, 'style', 'style=[font_weight=bold]' );
endcomp;

compute after _page_ /style={just=l 
							 font_size=10pt 
							 font_face='Times New Roman'  
							 borderbottomcolor=white 
							 bordertopcolor=black};
endcomp;

run;

My full code for Table S3 (working)

proc report	data=tem._NU_manuscript04_table&table_order.
					contents=""  /*remove default 2nd node*/
					nowd 
					split='|'
					style(report)={width=100%  
								   cellpadding=8px
								   font_face='Times New Roman' 
								   font_size=10pt 
								   borderwidth=1.5pt /* border thickness*/ 
								   background = TRANSPARENT}
				    style(header)={just=center 
								   font_weight=bold 
								   font_face='Times New Roman' 
								   font_size=10pt 
								   font_style=roman 
								   protectspecialchars = OFF 
								   background = TRANSPARENT}
					 style(column)={font_face='Times New Roman' 
									font_size=10pt 
									background = TRANSPARENT}
     ;

/*The COLUMN statement is used to list each report column*/
column gap01 	("\brdrb\brdrdot\brdrw5\brdrcf1 Trait 1" trait1_consortium trait1_substance trait1_name)
			gap02 	("\brdrb\brdrdot\brdrw5\brdrcf1 Trait 2" trait2_consortium trait2_substance trait2_name)
			OBSNO 
			gap03	("\brdrb\brdrdot\brdrw5\brdrcf1 Genetic correlations" dup_rG_p_value	rG_esti	rG_SE	rG_Z_score	rG_p_value)			
			; 

%def_group(	cVar=trait1_consortium		/*variable name*/
						,option=group			/*specify option=order if to order data alphabeticall; = group if not to order data*/
						,cName=Consortium  /*column header*/
						,isFmt=N					/*apply a format to the variable or not*/	 
						,cFmt=						/*which format*/	
						,cWide=1.25 cm
						,headerAlign=left 
						,colAlign=left	);

%def_display(	cVar=trait1_substance,	cName=Substance,			 	cWide=1 cm,	headerAlign= left, colAlign= left); 
%def_display(	cVar=trait1_name,		cName=Name,isFmt=N,cFmt= ,cWide=1.25 cm,	headerAlign= left, colAlign= left); 

%def_group(	cVar=trait2_consortium		/*variable name*/
						,option=group			/*specify option=order if to order data alphabeticall; = group if not to order data*/
						,cName=Consortium  /*column header*/
						,isFmt=N					/*apply a format to the variable or not*/	 
						,cFmt=						/*which format*/	
						,cWide=1.25 cm
						,headerAlign=left 
						,colAlign=left	);

%def_display(	cVar=trait2_substance,	cName=Substance,			 	cWide=1 cm,	headerAlign= left, colAlign= left); 
%def_display(	cVar=trait2_name,			cName=Name,isFmt=N,cFmt= ,cWide=1.25 cm,	headerAlign= left, colAlign= left); 

/*Each column, in turn, has a DEFINE statement that describes how that column is created and formatted.*/
DEFINE OBSNO/ ORDER NOPRINT	;

/*Set conditional variables to noprint*/
define dup_rG_p_value /noprint;

%def_display(	cVar=rG_esti,			cName=r^{sub G},isFmt=Y,cFmt=5.3,	cWide=0.75 cm,	headerAlign=right, colAlign=right);
%def_display(	cVar=rG_SE,			cName=SE,isFmt=Y,cFmt=5.3,	 			cWide=0.75 cm,		headerAlign=right, colAlign=right);
%def_display(	cVar=rG_Z_score,	cName=Z score,isFmt=Y,cFmt=5.3,	 	cWide=1 cm,		headerAlign=right, colAlign=right);
%def_display(	cVar=rG_p_value,	cName=p value,	 	cWide=2 cm,		headerAlign=right, colAlign=right);

/*define all the gap variables*/
%def_display(	cVar=gap01	,cName=		,isFmt=N	,cFmt=	,cWide=0.25%);
%def_display(	cVar=gap02	,cName=		,isFmt=N	,cFmt=	,cWide=0.25%);
%def_display(	cVar=gap03	,cName=		,isFmt=N	,cFmt=	,cWide=0.25%);

/*Bold rows if p values survive multiple testing 0.001111111 (0.05/45)
condition variable dup_rG_p_value must be on the left of the COMPUTE block variable rG_esti 
call define(_col_) applies the format on the compute variable column-wide
call define(_row_) applies the format across entire row
Every row is bolded. why?
*/
compute rG_esti;
	if dup_rG_p_value.sum < 0.001111111 then 
		do;
			call define(_row_, 'style', 'style=[font_weight=bold]' );
		end;
endcomp;

compute after _page_ /style={just=l 
							 font_size=10pt 
							 font_face='Times New Roman'  
							 borderbottomcolor=white 
							 bordertopcolor=black};
endcomp;

run;

My full code for Table S4 (working)

proc report	data=tem._NU_manuscript04_table&table_order.
					contents=""  /*remove default 2nd node*/
					nowd 
					split='|'
					style(report)={width=100%  
								   cellpadding=8px
								   font_face='Times New Roman' 
								   font_size=10pt 
								   borderwidth=1.5pt /* border thickness*/ 
								   background = TRANSPARENT}
				    style(header)={just=center 
								   font_weight=bold 
								   font_face='Times New Roman' 
								   font_size=10pt 
								   font_style=roman 
								   protectspecialchars = OFF 
								   background = TRANSPARENT}
					 style(column)={font_face='Times New Roman' 
									font_size=10pt 
									background = TRANSPARENT}
     ;

/*The COLUMN statement is used to list each report column*/
column ("\brdrb\brdrdot\brdrw5\brdrcf1 Exposure GWAS"  exposure_consortium exposure exposure_clumping_p1 ) 
			gap01 ("\brdrb\brdrdot\brdrw5\brdrcf1 Outcome GWAS" outcome_consortium outcome )   
			gap02 	("\brdrb\brdrdot\brdrw5\brdrcf1 IVW Estimators" nsnp OBSNO pval_dup method_abbreviated b se pval) /*ARROW, RIGHTWARDS	2192*/
			;

/*Each column, in turn, has a DEFINE statement that describes how that column is created and formatted.*/
%def_group(	cVar=exposure_consortium		
						,option=group			
						,cName=Sample
						,isFmt=N			
						,cFmt=				
						,cWide=1.25 cm
						,headerAlign=left 
						,colAlign=left	);

%def_group(	cVar=exposure	
						,option=group	
						,cName=Trait
						,isFmt=N
						,cFmt=	
						,cWide=1 cm
						,headerAlign=left 
						,colAlign=left	);

%def_group(	cVar=exposure_clumping_p1		/*variable name*/
						,option=group			/*specify option=order if to order data alphabeticall; = group if not to order data*/
						,cName=P value threshold  /*column header*/
						,isFmt=Y					/*apply a format to the variable or not*/	 
						,cFmt= e7.						/*which format? Ew. Format writes numeric values in scientific notation. range: 7-32*/	
						,cWide=2 cm
						,headerAlign=left 
						,colAlign=left	);

%def_group(	cVar=outcome_consortium		/*variable name*/
						,option=group			/*specify option=order if to order data alphabeticall; = group if not to order data*/
						,cName=Sample  /*column header*/
						,isFmt=N					/*apply a format to the variable or not*/	 
						,cFmt=						/*which format*/	
						,cWide=1.25 cm
						,headerAlign=left 
						,colAlign=left	);

%def_group(	cVar=outcome		/*variable name*/
						,option=group			/*specify option=order if to order data alphabeticall; = group if not to order data*/
						,cName=Trait  /*column header*/
						,isFmt=N					/*apply a format to the variable or not*/	 
						,cFmt=						/*which format*/	
						,cWide=1 cm
						,headerAlign=left 
						,colAlign=left	);

%def_group(	cVar=nsnp	/*variable name*/
						,option=group			/*specify option=order if to order data alphabeticall; = group if not to order data*/
						,cName=n SNPs  /*column header*/
						,isFmt=N					/*apply a format to the variable or not*/	 
						,cFmt=						/*which format*/	
						,cWide=1 cm
						,headerAlign=left 
						,colAlign=left	);
/*Set conditional variables to noprint. Place these variables after the group variables*/
DEFINE OBSNO / ORDER NOPRINT;
DEFINE pval_dup/display noprint;
DEFINE method_abbreviated/display noprint;

%def_display(	cVar=b,								cName= Beta,	isFmt=Y, cFmt=7.3,		cWide=1 cm,		headerAlign=left, colAlign=left);
%def_display(	cVar=se,								cName= SE,		isFmt=Y, cFmt=7.3,		cWide=1 cm,	headerAlign=right);
%def_display(	cVar=pval,							cName= p value,									cWide=2 cm,	headerAlign=right);

/*define all the gap variables*/
%def_display(	cVar=gap01	,cName=		,isFmt=N	,cFmt=	,cWide=0.25%);
%def_display(	cVar=gap02	,cName=		,isFmt=N	,cFmt=	,cWide=0.25%);

/*Conditionally highlight rows with p value < 0.0002777778 (0.05/180)
condition variable pvalueAge must be on the left of the COMPUTE block variable betaEstiAge
call define(_col_) applies the format on the compute variable column-wide
call define(_row_) applies the format across entire row
*/
compute method_abbreviated/character ;
	if pval_dup < 0.0002777778 then do;
		call define(_row_, 'style', 'style=[font_weight=bold]' );
	end;
endcomp;

compute after _page_ /style={just=l 
							 font_size=10pt 
							 font_face='Times New Roman'  
							 borderbottomcolor=white 
							 bordertopcolor=black};
endcomp;

run;

topic Conditionally formatting rows in a rtf file based on the values of a column with COMPUTE block in Statistical Procedures

Conditionally formatting rows in a rtf file based on the values of a column with COMPUTE block