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
This is what I have got: every value is bolded in Phenotypic
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--------------------------------------------------*/
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.
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.