tags: sas COMPUTE CALL DEFINE RTF control strings unicode first missing if countw %sysfunc %scan filename PROC IMPORT DEFINE PROC REPORT

Creating tables using SAS base programming

Chang's collection of working examples. Tables are styled in SAS ODS RTF and PROC REPORT.

Table of contents

Add a table of contents for tables^[1].

---

Table title

Put the first sentence in bold typeface^[2]

---

Bold the first sentence and use superscript^[3].

---

Table header

Use two-layer spanned header^[4].

---

Create three-layer spanned header^[5].

---

Use italic or superscript text as a shorthand^[6].

---

Table body

Add colored horizontal lines to split groups^[7]

---

Add blank lines to split groups^[8].

---

Add superscript letters to indicate levels of statistical significance^[9]. Superscript a indicates p value between 0.001 and 0.01. Superscript b indicates p value < 0.001.

---

Conditionally highlight selective columns based on values of a column^[10].

---

Conditionally bold rows based on values of a column^[11].

---

---

Keep rows with missing values and sort the data by a variable’s unformatted values^[12].

---

Tables in Excel files

Export SAS tables to an Excel file^[13]

---

PROC REPORT setting

By default, rows with missing values are removed from a table by PROC REPORT. To show the rows, specify MISSING option in the PROC REPORT.

• Example 11: How PROC REPORT Handles Missing Values

---

Table rows are sorted by PROC REPORT in an unknown manner. To order the table rows by values of a variable, specify DEFINE variable/ order order=internal

• ORDER, ORDER PLEASE: SORTING DATA USING PROC REPORT

---

Style the table of contetnts. This is to be done in MS Word, not in SAS.

• Text truncated in RTF Table of contents & changes of font face, size, color in TOC heading/body
• Creating Table of Contents in RTF Documents

Style the title using STYLE function

• ODS ESCAPECHAR Statement
• To bold the first sentence, simply add ^{style [fontweight=bold] to the beginning and } at the end
  "^{style [fontweight=bold]Table 1 Sources of data, sample sizes and types of analysis performed on substance use traits related to alcohol, caffeine, cannabis and nicotine.} UKB: UK Biobank. ICC: International Cannabis Consortium. GSCAN: GWAS & Sequencing Consortium of Alcohol and Nicotine use. OA: observational associations. GA: genetic analyses including SNP-based heritability estimation, genetic correlation, and two-sample Mendelian randomisation."

RTF control strings

Change font color of spanned headers using RTF control words

• SAS Notes and Concepts for ODS:The RTF Destination
• \cfn Specify foreground color; n means color code (1=black,2=blue). I haven't ask Cynthia where to look up color code n
• ODS RTF: the Basics and Beyond
• 3 layers of spanned headers in PROC REPORT

/*Show spanned headers that start with GSCAN in black (default).*/
/*Show spanned headers that start with UKB in blue by '\cf2'.*/
column	SNP_IDs 
		gap01 /*insert a gap between columns with two-level headers*/
		/*Significantly associated target and discovery traits by check marks in 3 sex groups*/
		("\brdrb\brdrdot\brdrw5\brdrcf1 GSCAN LD Clumping" GSCAN_LDClumped_si	GSCAN_LDClumped_ai	GSCAN_LDClumped_cpd	GSCAN_LDClumped_sc	GSCAN_LDClumped_dpw)
		gap02
		("\brdrb\brdrdot\brdrw5\brdrcf1\cf2 UKB LD Clumping" UKB_LDClumped_CPD	UKB_LDClumped_PYOS	UKB_LDClumped_ESDPW UKB_LDClumped_CCPD )
		gap03
		("\brdrb\brdrdot\brdrw5\brdrcf1\cf2 UKB GCTA-COJO" UKB_COJO_CPD UKB_COJO_PYOS UKB_COJO_ESDPW	UKB_COJO_CCPD )
			; 

Border control

Add a horizontal line under every value of a variable

• Horizontal lines in Proc Report

/*Insert black horizontal lines (as borders) under every value of SNP_IDs*/
compute SNP_IDs ;
       call define(_row_,'style', 'style={bordertopcolor=black bordertopwidth=1}');
endcomp;

---

Import data with separators

Import a tab-separated text file

/*File path of a tab-separated text file with 5 columns and 10 obs*/
filename table1 "D:\Now\library_genetics_epidemiology\GWAS\PRS_UKB_201711\GWASSummaryStatistics\GWAS_GSCAN\noQIMR_noBLTS_results\genetic_correlations\output_tabulated\rG_between-any-2-GSCAN-GWASs.tsv" 
			 encoding="utf-8"	;
/*Read the file in*/
proc import datafile=table1
	out=out.rG_bet_GSCAN_GWASs
	dbms=dlm
	replace;
	delimiter='09'x;
	getnames=YES;	
	GUESSINGROWS=10;
run;

---

A macro to select first non-missing value from people with multiple observations. For each elements of &variables, the macro outputs all the obs from input to a *_all dataset, outputs kept observations to a *_keep dataset, and outputs deleted observations to a *_drop dataset. The resulting *_keep dataset contains 1 observation per ID.

%SYSMACDELETE Select1stNonMissingDepVar;
%macro Select1stNonMissingDepVar (input_data=,variables=);
/*For each variable, select first non-missing values if there are multiple observations per ID. Output kept values to _keep data set. 
Output dropped observations to _drop dataset. Users need to combine all *_keep datasets by themselves*/
	%do i=1 %to %sysfunc(countw(&variables.)) ;
	%let var_name=%scan(&variables., &i.)	;
	data tem.noMis1wav_&var_name._all 		/*3398 (inconsistent with 374+711+2275)*/
			tem.noMis1wav_&var_name._keep		/*2956*/
			tem.noMis1wav_&var_name._drop	;	/*  442*/
		set &input_data.(keep=wave famID ID &var_name. age sex ageSq sexAge sexAgeSq zygosity);
		by ID;
		retain found	;
		if first.ID then found=0	; /*Mark first ID with found=0*/
		output tem.noMis1wav_&var_name._all; /*this data set contains all observations*/
		/*Mark first non-missing obs (wanted) with found=1. Else marked by found=0*/
		if found=0 AND (not missing(&var_name.) OR last.ID) then 
			do;
				found=1;				
				output	tem.noMis1wav_&var_name._keep;	 /*this data set contains wanted observations*/				
			end;		
		else output tem.noMis1wav_&var_name._drop; 	/*this data set contains unwanted observations*/
	run;
	%put var_name=&var_name. ;
%end;
%mend Select1stNonMissingDepVar;

%Select1stNonMissingDepVar(input_data=out.NU_nicotine_alcohol_disorder
						 ,variables= FTND1 FTND2 FTND3 FTND4 FTND5 FTND6 FTND_sum stem_nic );

Display Greek letters, symbols

• The Great Escape(char)
• Unicode and Special Characters
• italicize a single character and superscript the next character for a column header ODS RTF
• Symbols and unicodes used.

SAS code	Symbol
'^{unicode 2192}'	ARROW, RIGHTWARDS
'^{unicode beta}'	Greek beta
'^{sub xy}'	xy
'^{unicode 2713}'	check
'{style[fontstyle=italic]h}{unicode 00B2}'	h2
'^{unicode chi}^{unicode 00B2}'	X2

• Script: D:\Now\library_genetics_epidemiology\slave_NU\NU_analytical_programs_tables\NU4tabSup01_exposure-effect-on-outcome_SNP-effect-estimates.sas
• Table location: D:\Now\library_genetics_epidemiology\slave_NU\NU_analytical_output\NU4_tableSupplementary_byDate\MR1_tableSupp_2018-09-06.rtf
D:\Now\library_genetics_epidemiology\slave_NU\NU_analytical_output\NU4_tables_byDate\NU4_tables_2018-09-10.rtf

/*Add rightward arrows*/
proc report;
column exposure_label gap01	bxy gap02 snp
	gap03 ("\brdrb\brdrdot\brdrw5\brdrcf1 Genetic variant ^{unicode 2192} Exposure" bzx	bzx_se	bzx_pval) /*ARROW, RIGHTWARDS	2192*/
	gap04 ("\brdrb\brdrdot\brdrw5\brdrcf1 Genetic variant ^{unicode 2192} Outcome"	bzy	bzy_se	bzy_pval) ;

/*Add Greek letter beta and zy in subscript*/            
%def_display(cVar=bzx,cName=^{unicode beta}^{sub zx},cWide=1 cm,headerAlign=right);

/*Add a blank line under every exposure_label*/
compute after exposure_label ;
    line "";	
endcomp;

run;

/*Use unicode to show special character chi-squared*/
%def_display(cVar=X_squared_prevaBySex,	cName=^{unicode chi}^{unicode 00B2}, cWide=1.5 cm, headerAlign=right);

/*Add a special character for heritability h2*/
%def_display(cVar=h2_liab, cName=^{style[fontstyle=italic]h}^{unicode 00B2}, cWide=1 cm, headerAlign=right, colAlign=right); 

---

Multiple layers of spanned headers

• 3 layers of spanned headers in PROC REPORT

---

Conditionally highlight rows based on values of a column. The condition variable, evaluated by IF statement, must be on the left of a COMPUTE block variable (to be highlighted). Duplicate a variable if the condition variable is on the right.

• Conditionally formatting rows in a rtf file based on the values of a column with COMPUTE block
• script location: D:\Now\library_genetics_epidemiology\slave_NU\NU_analytical_programs_tables\NU4tab02_LDSC-genetic-correlation-results_licit-substance-exposure-GSCAN-UKB_outcome-cannabis-initiation-ICC.sas

/*The COLUMN statement is used to list each report column*/
column OBSNO dup_rG_p_value
			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)
			gap03	("\brdrb\brdrdot\brdrw5\brdrcf1 Genetic correlations"	rG_esti	rG_SE	rG_Z_score	rG_p_value)			
			; 

%def_display(	cVar=rG_esti,		cName=r^{sub G},isFmt=Y,cFmt=5.3,	cWide=0.75 cm,	headerAlign=right, colAlign=right);

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

/*Bold rows if p values survive multiple testing 0.001724138 (0.05/29)
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*/
compute rG_esti;
	if dup_rG_p_value.sum < 0.001724138 then	call define(_row_, 'style', 'style=[font_weight=bold]' );
endcomp;

Conditionally format a single or multiple columns based on the values of another (group of) column

• script location

/*Example 1: conditionally bold a single column
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 betaEstiAge /character	;
	if pvalueAge.sum < 0.05 then	call define(_col_, 'style', 'style=[font_weight=bold]' );
endcomp;

/*Example 2: conditionally format multiple columns. Present estimates in bold-face blue if the association p-values are lower than 0.0005882353*/

%macro conditionally_format_columns;
/*20 columns to process*/
%let variables_to_format=%STR(	si_fix_eff_estimate si_fix_eff_SE si_pvalue2sided si_R2
	ai_fix_eff_estimate ai_fix_eff_SE ai_pvalue2sided ai_R2 													cpd_fix_eff_estimate cpd_fix_eff_SE cpd_pvalue2sided cpd_R2
	sc_fix_eff_estimate sc_fix_eff_SE sc_pvalue2sided sc_R2
	dpw_fix_eff_estimate dpw_fix_eff_SE dpw_pvalue2sided dpw_R2)	; 
	/*20 condition variables at which the 20 variables above are computed agicolumns to process*/
%let condition_variables= %STR(dup_si_pvalue2sided dup_si_pvalue2sided dup_si_pvalue2sided dup_si_pvalue2sided    							  dup_ai_pvalue2sided dup_ai_pvalue2sided dup_ai_pvalue2sided dup_ai_pvalue2sided							dup_cpd_pvalue2sided dup_cpd_pvalue2sided dup_cpd_pvalue2sided dup_cpd_pvalue2sided
							   dup_sc_pvalue2sided	dup_sc_pvalue2sided	dup_sc_pvalue2sided dup_sc_pvalue2sided
					dup_dpw_pvalue2sided	dup_dpw_pvalue2sided dup_dpw_pvalue2sided dup_dpw_pvalue2sided ) ; 

%let n_var2format=%sysfunc(countw(&variables_to_format.));
%let corrected_p_threshold=0.0005882353;

%do i=1 %to &n_var2format.;
  %let var_01 = %scan(&variables_to_format., &i.);
  %let var_02 = %scan(&condition_variables., &i.);
  /*Here the columns to evaluate, referred by &var_02, are on the right of the COMPUTE block column, referred by &var_01.
			The following error if the columns to evaluate are on the left of the  COMPUTE block variable
				ERROR 180-322: Statement is not valid or it is used out of proper order.*/
  compute &var_01. ;
     if &var_02..sum < &corrected_p_threshold. then 
	/*_COL_ is the column that is associated with the COMPUTE block variable*/
		call define(_COL_, 'style', 'style=[foreground=blue font_weight=bold]' ); 
  endcomp;
%end;

%mend conditionally_format_columns;

%conditionally_format_columns

---

---

1. SAS script file at D:\Now\library_genetics_epidemiology\Chang_PhD_thesis\scripts\master-file_supplementary-tables.sas ↩︎

2. SAS script path
   D:\Now\library_genetics_epidemiology\Chang_PhD_thesis\SAS-supp-table-scripts\Ch2_tabSup01_count-twin-pairs_binary-outcomes.sas ↩︎

3. SAS script path
   D:\Now\library_genetics_epidemiology\Chang_PhD_thesis\SAS-supp-table-scripts\Ch3_tabSup03_fixed-effect-etimates-GSCAN-PRSs-on_illicit-drug-AU-CU-QIMR19Up.sas ↩︎

4. SAS script file at D:\Now\library_genetics_epidemiology\Chang_PhD_thesis\SAS-supp-table-scripts\Ch3_tabSup03_fixed-effect-etimates-GSCAN-PRSs-on_illicit-drug-AU-CU-QIMR19Up.sas ↩︎

5. SAS script file at D:\Now\library_genetics_epidemiology\slave_NU\NU_analytical_programs_tables\NU4tab06_MR-leave-one-out-analysis_results.sas ↩︎

6. SAS script file at D:\Now\library_genetics_epidemiology\Chang_PhD_thesis\SAS-supp-table-scripts\Ch5_tabSup01_GWAS-sample-size-prevalence_LDSC-SNP-heritability-estimates.sas ↩︎

7. SAS script file at D:\Now\library_genetics_epidemiology\Chang_PhD_thesis\SAS-supp-table-scripts\Ch2_tabSup01_count-twin-pairs_binary-outcomes.sas ↩︎

8. SAS script file at D:\Now\library_genetics_epidemiology\Chang_PhD_thesis\SAS-supp-table-scripts\Ch2_tabSup02_1VarBin_basiAssum_difLL_significance.sas ↩︎

9. SAS script file at D:\Now\library_genetics_epidemiology\Chang_PhD_thesis\SAS-supp-table-scripts\Ch2_tabSup03_1VarCon_basiAssum_difLL_significance.sas ↩︎

10. SAS script file at D:\Now\library_genetics_epidemiology\Chang_PhD_thesis\SAS-supp-table-scripts\Ch3_tabSup03_fixed-effect-etimates-GSCAN-PRSs-on_illicit-drug-AU-CU-QIMR19Up.sas ↩︎

11. SAS script files at D:\Now\library_genetics_epidemiology\Chang_PhD_thesis\SAS-supp-table-scripts\Ch5_tabSup02_two-sample-MR-results_non-cannabis-initiation_as_exposure_or_outcome.sas D:\Now\library_genetics_epidemiology\Chang_PhD_thesis\SAS-supp-table-scripts\Ch5_tabSup03_odds-ratio_exposure-UKB-CCPD-ESDPW-PYOS_outcome-ICC-CI_MR-sensitivity-analyses.sas ↩︎

12. SAS script file at D:\Now\library_genetics_epidemiology\slave_NU\NU_analytical_programs_tables\NU4tab06_MR-leave-one-out-analysis_results.sas ↩︎

13. SAS script file path
    D:\z_old_files\national_inpatient_sample\NIS_analytical_programs\NIS_101_export_tables.sas ↩︎