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;
What is ANOVA?
ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.
Find more tutorials on the SAS Users YouTube channel.
-
1373 views
-
0 likes
-
-
