OPTIONS LINESIZE=140 PAGESIZE=100 NOCENTER NODATE NONUMBER NOFMTERR fmtsearch=(current.w2form);;
**********************************************************************;
*                 WATCH II Study PROGRAM                              ;
*          PROGRAM NAME        LOCATION  DATE   PROGRAMMER            ;
Title1  "Source:w2py04p71.SAS  WATCH II 4/18/2004   EJS,PY"       ;
*           Create Descriptive Table Macro for a continuous variable  ;
*          with two treatment groups at baseline and follow-up        ;
*        Create Macro: Title_t2:  Creates Title for table2            ;
*                      Table2: Creates summary mean and test at       ;
*                              two times for two groups for Female    ;
*                      It handles when there is a space in variable   ;
*                      name, such as Direct LDL.                      ;
*    Use: w2measur.sas7bdat,demv8 Analysis data set for WATCH II study;

FILENAME tout "J:\Projects\watchII\temp\peikang\output\Table2_F.txt"  ;         
*******************************************************************;
LIBNAME current v8 "J:\Projects\watchII\data\current";
ODS HTML CLOSE;
ODS Listing ;

**************************************************************;
*** Read in basic data and limit it to eligible cases         ;
**************************************************************;

DATA d1;
	SET current.demv8 (KEEP=id income schoolyr status agev2 condition eligv3 female racethn);
  	IF eligv3=1;  *select only eligible subjects;
  	IF FEMALE=1; *select female;
  
******************************************;
*** Create indicator variable for followup data;
***  using cholesterol and follow-up questionnarie;
***   First see if cholesterol is not missing     ;
**************************************************;

DATA c1 c2;
	SET current.w2measur (KEEP=id timeper bmi weight);  
  	IF timeper=1 THEN OUTPUT c1;  * Subjects with baseline cholesterol;
    IF timeper=2 THEN OUTPUT c2;  * Subjects with follow-up cholesterol;
run;

PROC MEANS DATA=c1;
  TITLE2 "Table 1.  Means of baseline Chol Measures";
  RUN;
PROC MEANS DATA=c2;
  TITLE2 "Table 1.  Means of FU Chol Measures";
  RUN;

******************************************;
*** Combine with eligibility codes to     ;
***  create cholesterol follow-up indicator;
*******************************************;
PROC SORT DATA=c1;  BY id;
PROC SORT DATA=c2;  BY id;
PROC SORT DATA=d1;  BY id;

*********************************************************
*** merge baseline with cholestrol with deogramphic data;
********************************************************;
DATA dc1 (KEEP=id bmi weight); * need bim and weight;
  MERGE c1 (in=a) d1(KEEP=id eligv3);
  BY id;
  IF a;         * Only keep from Chol data;
  IF eligv3=1;  *select eligible cases;
run;

*********************************************************
*** merge follow up with cholestrol with deogramphic data;
*********************************************************;
DATA dc2 (KEEP=id chol_fu);
	MERGE c2 (IN=a) d1(KEEP=id eligv3);
  	BY id;
  	IF a;         * Only keep from Chol data;
  	IF eligv3=1;  *select eligible cases;
  	chol_fu=1;    *Indicator for follow-up cholesterol;
run;

*********************************************************
*** merge baseline and follow up with cholestrol, with 
*** additional indicator for follow-up cholesterol;
********************************************************;
DATA dc12 (KEEP=id chol_fu bmi weight);
  	MERGE dc1 dc2;
  	BY id;
run;

*****************************************;
*** Add indicator of cholesterol follow-up;
******************************************;
DATA d2;
	MERGE d1 dc12;
  	BY id;
  	bmifu=bmi*chol_fu; *equal to missing if no follow-up chol;
  	weightfu=weight*chol_fu;
RUN;
PROC MEANS DATA=d2;
  	TITLE2 "Table 2. List of means for demographic data";
RUN;

******************;
*** Generate title;
******************;

%MACRO title_t2 (tableout);
	DATA _NULL_;
		FILE &tableout;
		PUT   @1 "Table 3. Simple statistics and Significance test of difference in Anthropometric, Dietary and"/
		   "Activity variables between intervention and control at baseline and 1 yr follow up for Female"/
	    "__________________________________________________________________________________________________"/
        "__________________________________________________________________________________________________"/
	 	@1 "Variable                       Baseline                              Follow-up"/;
		PUT    @7 "           Overall   Control  Intervention"@52 "p-value" "    Control  Intervention"@86 "p-value"/
        "________________________________________________________________________________________________"/
        "________________________________________________________________________________________________";
	RUN;
%MEND;

**************************************************
*** generate table with following similar contents
**************************************************;

*************************************************************************************************
   	Variable                     Baseline                           Follow-up
              Overall     Control   Intervention               Control    Intervention
              n=792        n=401        n=391    p-value        n=364         n=347    p-value
              v_n          v1_n          v2_n                    f1_n         f2_n
 Age   Mean   45.0         44.9         45.0     .8605          45.5          45.6     .8605
              v_m          v1_m         v2_m      v_p           f1_m          f2_m      f_p
       (Std) (11.7)       (11.5)       (11.8)                  (11.5)        (11.7)
              v_sd         v1_sd       v2_sd                    f1_sd         f2_sd 
*23456789112345678921234567893123456789412345678951234567896123456789712345678981234567899123456789;
***************************************************************************************************;

%MACRO Table2(datain=, varin=, class1=, varfu=, varn=, tableout=);
	*******************************************************;
	*** Summarize variable at baseline                   **;
	*******************************************************;
	PROC SUMMARY NOPRINT DATA=&datain;
  		VAR &varin ;
  		CLASS &class1;
  		OUTPUT OUT=x2;
  	**************************************;
  	*** Get means for each group         *;
  	**************************************;
	PROC TRANSPOSE DATA=x2  OUT=x3 (RENAME=( col1=v_n    col4=v_m    col5=v_sd
                                         col6=v1_n   col9=v1_m  col10=v1_sd
                                         col11=v2_n col14=v2_m  col15=v2_sd)
                                		 KEEP= col1  col4  col5 
                                     	 col6  col9  col10
                                      	col11 col14 col15);
    	VAR &varin ;
  	********************************************;
  	*** Test equal age by intervention at baseline;
  	**********************************************;
	PROC TTEST DATA=&datain;
  		CLASS &class1;
  		VAR &varin;
  		ODS OUTPUT TTESTS=e1 (RENAME=(probt=v_p) 
                          KEEP=probt method );
	DATA e2 (DROP=method); 
  		SET e1; 
  		IF method="Pooled";
	*******************************************************;
	*** Summarize variable at Follow-up                  **;
	*******************************************************;
	PROC SUMMARY NOPRINT DATA=&datain;
  		VAR &varfu ;
  		CLASS &class1;
  		OUTPUT OUT=f2;
  	**************************************;
  	*** Get means for each group         *;
  	**************************************;
	PROC TRANSPOSE DATA=f2  OUT=f3 (RENAME=(  col6=f1_n  col9=f1_m  col10=f1_sd
                                col11=f2_n col14=f2_m  col15=f2_sd)
                                KEEP=  col6  col9 col10
                                col11 col14 col15);
    	VAR &varfu ;
  	********************************************;
  	*** Test equal age by intervention at baseline;
  	**********************************************;
	PROC TTEST DATA=&datain ;
  		CLASS &class1;
  		VAR &varfu;
  		ODS OUTPUT TTESTS=fe1 (RENAME=(probt=f_p) 
                          KEEP=probt method );
	DATA fe2 (DROP=method); 
  		SET fe1; 
  		IF method="Pooled";
	*****************************************;
	*** Combine data prior to printing table ;
	*****************************************;
	DATA x4; 
  		MERGE x3 e2 f3 fe2;
	RUN;
	DATA _NULL_;
  		SET x4;
	

   FILE &tableout MOD;

	*** deal with some special case which places variable name in two lines;
    *** take apart string if there is space between string;
    space=verify("&varn", ' '); *if return 1 then there is space, 0, otherwise.;
	
	if space eq 1 then do;
		varn1=scan("&varn", 1,' ');
		varn2=scan("&varn", 2,' ');
	end;
	
	IF space = 1 then
		PUT @1 varn1 @11 "Mean"   @19  v_m 4.1              @30  v1_m 4.1            @42  v2_m 4.1   @52  v_p 5.4
	                             @65 f1_m 4.1                @76 f2_m 4.1          @87 f_p 5.4 /
		@1 varn2   @10"(Std)"  @18  "(" v_sd    4.2 ")"  @29  "(" v1_sd   4.2 ")" @41 "(" v2_sd   4.2 ")"
                                 @64 "(" f1_sd 4.2 ")"       @75 "(" f2_sd 4.2 ")"   /
		@10"  n "   @19  v_n 3.0          @30  v1_n 3.0        @42  v2_n 3.0
                                 @65  f1_n 3.0               @76  f2_n 3.0  /
	"_________________________________________________________________________________________________"/;

	else 
		PUT @1 "&varn"     @11 "Mean"   @19  v_m 4.1              @30  v1_m 4.1            @42  v2_m 4.1   @52  v_p 5.4
	                             @65 f1_m 4.1                @76 f2_m 4.1          @87 f_p 5.4 /
		@10"(Std)"  @18  "(" v_sd    4.2 ")"  @29  "(" v1_sd   4.2 ")" @41 "(" v2_sd   4.2 ")"
                                 @64 "(" f1_sd 4.2 ")"       @75 "(" f2_sd 4.2 ")"   /
		@10"  n "   @19  v_n 3.0          @30  v1_n 3.0        @42  v2_n 3.0
                                 @65  f1_n 3.0               @76  f2_n 3.0  /
	"_________________________________________________________________________________________________"/;
 
	RUN;
%MEND;

******************
*** Add sources  ;
*****************;
%MACRO source (tableout);
	DATA _NULL_;

	FILE &tableout MOD; 
	PUT   "__________________________________________________________________________________________________"/
	  @1"     "/	
	  @1"Source:" " W2py04p69.sas, W2py04p68.sas"/;

	RUN;
%MEND;

***************
*** sample run;
**************;

%title_t2 (tout);
%Table2(datain=d2, varin=bmi, class1=condition, varfu=bmifu, varn=BMI, tableout=tout);
%Table2(datain=d2, varin=weight, class1=condition, varfu=weightfu, varn=Weight, tableout=tout);