The SAS Output Delivery System and reporting techniques

conditionally apply a style to a column in PROC REPORT COMPUTE block

Reply
Contributor
Posts: 61

conditionally apply a style to a column in PROC REPORT COMPUTE block

[ Edited ]

hi,

 

I have a dataset that contain correlation coefficients and p values. I would like to bold the values in column "Phenotypic" (variable name: PSYCH6_rP95CI) when their corresponding p values (variable name: PSYCH6_rPPvalue1) are smaller than 0.05. The variable PSYCH6_rP95CI is a character variable. I included all the needed variables in the COLUMN statement. I don't need the p values on my report so I specified the noprint option in the DEFINE statement for p value. In the compute block, I created the bolding condition and specified the style. Unfortunately, all the values of PSYCH6_rP95CI were bolded. I didn't get any error message. I had successfully bolded rows with numeric variables but I don't seem to have any luck with character variables now.

 

This is my desirable output which I have bolded the values for Phenotypic in MS WORD

PROC_REPORT_conditional_bold_column01_want.png

 

This is what I have got: every value is bolded in Phenotypic

PROC_REPORT_conditional_bold_column02_allBolded.png

 

My data and SAS code:

 

There are 4 parts in my script. The part 1 generates the data for the table. Part 2 create 2 macros for repetitive DEFINE statements. Part 3 specifies setting across the entire RTF file. Part 4 is the code for generating the table. The script file, including the data is attached.

 

/*------------------------------------------part 1: getting data--------------------------------------------------------*/
/*input and output directory*/
libname out 			"D:\Now\library_genetics_epidemiology\slave_NU\NU_data_processed"		;

data OUT.BIVAR_CORR_PSYCH6IRT_SOMA6IRT;
  infile datalines dsd truncover;
  input rownum:32. depVar1:$27. depVar2:$27. PSYCH6_rPPvalue1:5.3 PSYCH6_rPPvalue2:5.3 PSYCH6_rGPvalue1:5.3 PSYCH6_rGPvalue2:5.3 PSYCH6_rEPvalue1:5.3 PSYCH6_rEPvalue2:5.3 PSYCH6_rP95CI:$25. PSYCH6_rG95CI:$25. PSYCH6_rE95CI:$25. SOMA6_rPPvalue1:5.3 SOMA6_rPPvalue2:5.3 SOMA6_rGPvalue1:5.3 SOMA6_rGPvalue2:5.3 SOMA6_rEPvalue1:5.3 SOMA6_rEPvalue2:5.3 SOMA6_rP95CI:$25. SOMA6_rG95CI:$25. SOMA6_rE95CI:$25.;
datalines4;
1,<13,13-15,0.000,0.000,0.000,0.017,0.035,0.000,0.30 ( 0.24 -  0.36),0.61 ( 0.39 -  0.85),0.13 ( 0.01 -  0.25),0.000,0.000,0.000,0.092,0.253,0.000,0.38 ( 0.32 -  0.44),0.81 ( 0.64 -  0.99),0.07 (-0.05 -  0.19)
2,<13,15-17,0.000,0.000,0.000,0.011,0.876,0.000,0.20 ( 0.13 -  0.27),0.61 ( 0.30 -  0.94),0.01 (-0.13 -  0.15),0.000,0.000,0.000,0.008,0.956,0.000,0.31 ( 0.24 -  0.37),0.80 ( 0.57 -  1.00),0.00 (-0.14 -  0.15)
3,<13,17-19,0.681,0.004,0.709,0.549,0.428,0.145,-0.30 (-0.71 -  0.48),0.32 (-1.00 -  1.00),-0.70 (-0.94 -  0.77),0.002,0.000,0.264,0.000,0.001,0.014,0.41 ( 0.17 -  0.75),-0.76 (-1.00 -  0.34),0.99 ( 0.89 -  1.00)
4,<13,19+,0.008,0.000,0.027,0.019,0.971,0.000,0.15 ( 0.05 -  0.24),0.48 ( 0.05 -  0.93),0.00 (-0.18 -  0.19),0.023,0.000,0.468,0.000,0.142,0.000,0.14 ( 0.04 -  0.24),0.15 (-0.26 -  0.56),0.14 (-0.05 -  0.33)
5,13-15,15-17,0.000,0.000,0.000,0.154,0.000,0.000,0.37 ( 0.31 -  0.43),0.79 ( 0.50 -  1.00),0.22 ( 0.10 -  0.33),0.000,0.000,0.000,0.001,0.001,0.000,0.41 ( 0.35 -  0.46),0.73 ( 0.54 -  0.92),0.21 ( 0.08 -  0.32)
6,13-15,17-19,0.208,0.029,0.165,0.754,0.758,0.075,0.22 (-0.22 -  0.63),1.00 (-0.39 -  1.00),-0.19 (-0.75 -  0.66),0.021,0.000,0.019,0.044,0.180,0.014,0.54 ( 0.18 -  0.74),1.00 ( 0.29 -  1.00),0.35 (-0.19 -  0.64)
7,13-15,19+,0.000,0.000,0.004,0.117,0.984,0.000,0.17 ( 0.08 -  0.26),0.63 ( 0.21 -  1.00),0.00 (-0.15 -  0.16),0.000,0.000,0.022,0.001,0.029,0.000,0.27 ( 0.19 -  0.36),0.44 ( 0.07 -  0.80),0.20 ( 0.02 -  0.37)
8,15-17,17-19,0.702,0.848,0.440,0.752,0.538,0.681,-0.03 (-0.67 -  0.65),-1.00 (-1.00 - -0.96),0.41 (-0.62 -  0.95),0.956,0.000,0.773,0.027,0.706,0.164,0.16 (-0.62 -  0.64),1.00 (-1.00 -  1.00),-0.16 (-0.73 -  0.91)
9,15-17,19+,0.000,0.000,0.002,0.183,0.036,0.000,0.28 ( 0.20 -  0.34),0.68 ( 0.26 -  1.00),0.15 ( 0.01 -  0.29),0.000,0.000,0.016,0.000,0.001,0.000,0.29 ( 0.21 -  0.35),0.40 ( 0.08 -  0.70),0.24 ( 0.10 -  0.37)
10,17-19,19+,0.000,0.000,0.004,0.194,0.028,0.000,0.35 ( 0.25 -  0.43),0.69 ( 0.24 -  1.00),0.21 ( 0.02 -  0.38),0.000,0.000,0.084,0.045,0.006,0.000,0.31 ( 0.22 -  0.39),0.49 (-0.08 -  1.00),0.25 ( 0.07 -  0.42)
;;;;

/*--------------------------part 2: create macros for repetitive DEFINE statements------------------------------------*/
%macro def_display(	cVar		/*character variable*/	
					,cName					/*name of the character variable*/	
					,isFmt					/*apply a format to the variable or not*/	
					,cFmt					/*which format*/	
					,cWide=25pt		/*cell width*/	
					);

	define	&cVar.	/	display	"&cName."	
		%if &isFmt.=Y %then 
			%do;
					format= &cFmt.
					style(header)={just=left}
					style(column)={just=left cellwidth= &cWide.}	;
			%end;
		%else	
			%do;
					style(header)={just=left}
					style(column)={just=left cellwidth= &cWide.} ;
			%end; 
%mend def_display;

%macro def_group(	cVar		/*character variable*/
					,option					/*option= order : duplicated rows shown as blank, SAS will reorder your data */
												/*option= group : duplicated rows shown as blank, SAS doesn't reorder your data */
					,cName					/*name of the character variable*/	
					,cWide=25pt		/*cell width*/	
					);

	define	&cVar.	/	&option. "&cName."  order=data 
			style(column)={just=left cellwidth= &cWide.};	/*replace order with &option. in the future*/
	
%mend def_group;

/*--------------------------------part 3: setting for entire report---------------------------*/
options	missing = ' '  	/*Specifies the character to print for missing numeric values.*/ 
			center 			/*Specifies whether to center or left align SAS procedure output*/
			nodate 			/*suppress default print of data time that this SAS program executed*/
			nonumber 	/*suppress default print of page number on first title line of each page*/
			orientation=landscape
			/*orientation=portrait*/
		;
/*changing table margins*/
options	bottommargin = 	0.3in 
   		topmargin = 				0.2in 
   		rightmargin = 			2 cm /*0.5in */
   		leftmargin = 				2 cm /*0.5in */
		; 

/*suppress the warning that a quoted string that exceeds 262 characters in length*/
options NOQUOTELENMAX ; 

/*specify output file*/
ods _all_ close; 
ods rtf file=		"C:\Users\chang\tables_test_%sysfunc(date(), yymmdd10.).rtf" 
		style=		Journal 
		contents 
		toc_data ; /*show table of contents*/

/* tell ODS what character will be used as the ODS ESCAPECHAR character value*/
ods escapechar='~';
ods escapechar='^';

/*Add study name at left end of 2nd line*/
title2	j=l 										/*left-aligned*/
			font='Times New Roman' 
			h=10pt 'Study: The 19 (NU) Up Study'
	;	
/*Add page number at right end of line 3*/
title3 j=R  /*right-aligned*/
		font='Times New Roman' 
		h=10pt 'Page ~{thispage} of ~{lastpage}'
	;

/*--------------------------------part 4: setting for the body of this table---------------------------*/
/*import data*/
data out._NU_table_12b;
	set OUT.BIVAR_CORR_PSYCH6IRT_SOMA6IRT	;
	gap01='';	/*add gap01, gap02 for inserting blank in PROC REPORT*/
	gap02='';
	TT=1; /*insert this variable for breaking pages*/
run;

/*Add titles and footnotes*/
proc sort data=	out._NU_table_12b; by TT; run;

/*change a procedure label*/
ods proclabel="table 8"	;
 
/*first node kept and used in table of contents*/
title4 J=L /*this is SAS data set name*/
		font='Times New Roman' 
		h=10pt "Data Source: OUT.BIVAR_CORR_PSYCH6IRT_SOMA6IRT" 
	;  

title5 J=L /*this is the table title*/
		font='Times New Roman' 
		h=10pt "table 8" 
	;  
footnote j=l /*this is the footnote*/
			 font='Times New Roman' 
			 h=10pt "Program Name: table8.sas.			Programmer: Chang			Date:%sysfunc(date(),worddate18.)"	
	;

/*add table body*/
proc report	data=out._NU_table_12b
					contents=""  /*remove default 2nd node*/
					nowd 
					split='|'
					style(report)={width=100%  
								   cellpadding=8px
								   font_face='Times New Roman' 
								   font_size=10pt 
								   background = TRANSPARENT}
				    style(header)={just=left 
								   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 Age groups" depVar1 depVar2)

	gap01 /*insert a gap between columns with two-level headers*/

	("\brdrb\brdrdot\brdrw5\brdrcf1 PSYCH6 correlation coefficient (95% CI)"
	PSYCH6_rPPvalue1	PSYCH6_rGPvalue1	PSYCH6_rEPvalue1
	PSYCH6_rP95CI			PSYCH6_rG95CI		PSYCH6_rE95CI		 				) 

	gap02 /*insert a gap between columns with two-level headers*/
	 ("\brdrb\brdrdot\brdrw5\brdrcf1 SOMA6 correlation coefficient (95% CI)"
		SOMA6_rP95CI SOMA6_rG95CI SOMA6_rE95CI )	
		; 

/*Each column, in turn, has a DEFINE statement that describes how that column is created and formatted.*/
%def_group(	cVar=depVar1,			option=group,	cName=1,	cWide=1.5 cm);

%def_display(	cVar=depVar2,			cName=2,	isFmt=N	,cFmt=, cWide= 1.5 cm);

define PSYCH6_rPPvalue1 / noprint ;
define PSYCH6_rGPvalue1 / noprint ;
define PSYCH6_rEPvalue1 / noprint ;

%def_display(	cVar=PSYCH6_rP95CI,		cName=Phenotypic,			isFmt=N	,cFmt=, cWide=3 cm);
%def_display(	cVar=PSYCH6_rG95CI,		cName=Genetic,				isFmt=N	,cFmt=, cWide=3 cm);
%def_display(	cVar=PSYCH6_rE95CI,		cName=Environmental,	isFmt=N	,cFmt=, cWide=3 cm);

%def_display(	cVar=SOMA6_rP95CI,		cName=Phenotypic,			isFmt=N	,cFmt=, cWide=3 cm);
%def_display(	cVar=SOMA6_rG95CI,		cName=Genetic,				isFmt=N	,cFmt=, cWide=3 cm);
%def_display(	cVar=SOMA6_rE95CI,		cName=Environmental,	isFmt=N	,cFmt=, cWide=3 cm);

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

/*bold correlation (95% CI) with significant pvalue1*/
/*When creating a computed character variable its attributes must be declared with the character keyword*/
compute PSYCH6_rP95CI / character length=25;
	if PSYCH6_rPPvalue1 < 0.05	then call define(_COL_, "style", 'style={fontweight=bold}' );
endcomp;

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

	/*break before TT/ page contents="";*/
		/*remove 3rd node*/
run;

/*---------------------------------------This is the end of this program--------------------------------------------------*/

 

Attachment
Grand Advisor
Posts: 9,748

Re: conditionally apply a style to a column in PROC REPORT COMPUTE block

Can you provide the code without undefined macros? We have no idea what your %def macros may be contributing to the issue.

 

It is also better to post example data in the form of a data step using the instructions available here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... as many folks do not want to download random zip or other binary files or may have policies in place to block that behavior.

Contributor
Posts: 61

Re: conditionally apply a style to a column in PROC REPORT COMPUTE block

hi ,

 

I have updated my code. It has included the data portion in its part 1.

SAS Super FREQ
Posts: 8,647

Re: conditionally apply a style to a column in PROC REPORT COMPUTE block

Hi:
Remember that PROC REPORT works from LEFT to RIGHT with your variables in the COLUMN statement...for example:
column var1 var2 var3 var4 var5;

In a compute block for VAR2, you can only test the value of VAR1, because when PROC REPORT is handling VAR2, it does not yet have values on the report row for VAR3, VAR4 or VAR5.

Similarly, in the COMPUTE block for VAR3, you can only test the values of VAR1 and VAR2.

PROC REPORT works from left to right. So that means your pvalue variable that you want to use in the test has to appear in the column statement to the LEFT of the COMPUTE block column.

Your problem has nothing to do with character or numeric variables, I believe you have misunderstood how comparisons in the COMPUTE block are tied to the order of the variables in the COLUMN statement.

cynthia
Contributor
Posts: 61

Re: conditionally apply a style to a column in PROC REPORT COMPUTE block

hi Cythia,

 

I have changed the variable order in my COLUMN statement. However, I am getting the same all-bolded column in my exported RTF. I have updated the code in my original post. It has the data portion now.

SAS Super FREQ
Posts: 8,647

Re: conditionally apply a style to a column in PROC REPORT COMPUTE block

Hi:

  What is the usage for the variable that you are testing? For example, in the code below, report #1 does not work because the COMPUTE block does not use the right reference for AGE. The way you refer to a numeric variable depends on how you specify the usage in the DEFINE statement. If you don't have a usage in the DEFINE, then you have to understand the defaults in order to code correctly. See how Report #1 has the style applied to every row, no matter what the value for AGE is. On the other hand, #2 or #3 reports are both correct. #2 is correct because it uses the compound reference AGE.SUM given no usage other than the default for AGE; and #3 is correct because a usage of DISPLAY was provided in the DEFINE statement, which allowed the simple reference in the IF.

 

cynthia

 

ods html file='c:\temp\do_compute.html';
proc report data=sashelp.class;
title '1) compute block does not use compound name';
column name age height weight;
define name / order;
define age / 'Age' ;
define height / 'Height';
define weight / 'Weight';
compute height;
  if age le 12 then call define(_col_,'style','style={background=red}');
endcomp;
run;

proc report data=sashelp.class;
title '2) compute block does use compound name because default statistics for age is SUM';
column name age height weight;
define name / order;
define age / 'Age' ;
define height / 'Height';
define weight / 'Weight';
compute height;
  if age.sum le 12 then call define(_col_,'style','style={background=red}');
endcomp;
run;

proc report data=sashelp.class;
title '3) compute block works because usage of AGE is DISPLAY';
column name age height weight;
define name / order;
define age / display 'Age' ;
define height / 'Height';
define weight / 'Weight';
compute height;
  if age le 12 then call define(_col_,'style','style={background=red}');
endcomp;
run;
ods html close;
Post a Question
Discussion Stats
  • 5 replies
  • 318 views
  • 0 likes
  • 3 in conversation