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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.