Exporting Proc ttest to Excel

[Wolverine]

I have a series of about 45 t-tests I'm trying to run (I've only shown 2 here).  All I really need is the mean, 95% confidence intervals, and p-value for each.  I'd like to export them to an Excel file with the pair name in Column A, the mean in B, 95% confidence intervals in C and D, and p-value in E.  This is what I have so far, but the SAS output still has more information than I need, and it results in a blank Excel file.

ods graphics on;
title1 "T-tests";
PROC TTEST plots=none
			data=temp.SVI_hyst_output_SAS_dum;
	paired 	age_cat1_lt40*SVI_high_flag 
			age_cat1_lt40*SVI_low_flag;
RUN;


ODS Excel file="Z:\Programming and analysis\ttest_v&version. RAW.xlsx"
	OPTIONS(embedded_titles="YES"
		embedded_footnotes="YES"
		start_at="1,2"
		frozen_headers="YES"  );
	TITLE1 "Ttest TEST V(&version.)";
	TITLE2 "&sysdate.";
	RUN;
ODS Excel close;

ods graphics off;

[Sajid01]

Re arrange your code like this

ds graphics on;
title1 "T-tests";
ODS Excel file="Z:\Programming and analysis\ttest_v&version. RAW.xlsx"
	OPTIONS(embedded_titles="YES"
		embedded_footnotes="YES"
		start_at="1,2"
		frozen_headers="YES"  );
	TITLE1 "Ttest TEST V(&version.)";
	TITLE2 "&sysdate.";
PROC TTEST plots=none
			data=temp.SVI_hyst_output_SAS_dum;
	paired 	age_cat1_lt40*SVI_high_flag 
			age_cat1_lt40*SVI_low_flag;
RUN;	
ODS Excel close;

ods graphics off;

[Wolverine]

That populates the Excel file, but it creates 3 separate tabs for each pair of variables (statistics, confidence intervals, and t-tests). I'd really just like 1 tab with about 45 lines, each with the 5 columns I indicated above.

[Sajid01]

If the objective is to have all the results  in a single sheet, use the option sheet_interval="None" in the ODS  Excel options and better remove the frozen_headers options. Everything comes on a single sheet.

Illustrating by means of a simple example. Modify as needed.

data swim;
input Swimmer $ TrialTime FinalTime SemiFinalTime @@;
datalines;
RK 24.07 24.05 24.07 AH 24.34 24.28 24.45 MV 24.41 24.39 24.50 BS 24.01 24.46 24.57
FH 24.31 24.47 24.63 TA 25.01 24.61 24.71 JH 24.01 24.62 24.68 AV 24.32 24.69 24.64
;
run;
/*Data adapted from the Little SAS Book A Primer 5hh Edition p261*/
ODS Excel file="/home/username/ttest.xlsx"
	OPTIONS(embedded_titles="YES"
		embedded_footnotes="YES"
		start_at="1,2" 
		sheet_interval="None");
PROC TTEST plots=none DATA=Swim ;
   TITLE '50m Freestyle Semifinal vs. Final Results';
    PAIRED  TrialTime* (FinalTime SemiFinalTime ); 
RUN;
ODS Excel close;

[ballardw · Accepted solution]

Lets discuss the last bit first.

Your ODS EXCEL has no content because ODS output has to be generated between the destination and close statement. Since you have nothing creating output between ODS Excel and Ods excel close then the file is empty.

Depending on what you actually want (without data we cannot see what your code currently creates) you may want to either use ODS SELECT <tablename> to limit the output created by proc ttest (or any other procedure). But since you are specifying columns that means that you likely want to direct the output to data sets and massage them into the order you want then use a report procedure like Print, Report or tabulate to display the desired values.

An example, not particularly useful due to the nature of the variables used, but demonstrates a basic approach using a data set you should have so you can run it and see results.

ods output conflimits=work.conf
           ttests    =work.test
;
PROC TTEST plots=none
			  data= sashelp.class;
	paired 	age *(height weight);
RUN;

Proc sort data=work.conf;
   by difference;
run;

Proc sort data=work.test;
   by difference;
run;

data want;
   merge work.conf
         work.test
   ;
   by difference;
run;

proc print data=want;
   var difference  mean Lowerclmean upperclmean probt;
run;

The Proc Print would be the part that goes in the ODS Excel part.

Mean of what? Each variable? The difference for each pair? There are enough values floating around that you may need to be more specific on this. Same with confidence limits

Either provide an example data set in form of data step code or use a SAS supplied data set to demonstrate what you want.

You may want to know that the Paired statement supports syntax similar to the Proc Freq tables statement to short hand combinations of variables and reduce typing. Your Paired statement could be written

paired 	age_cat1_lt40*(SVI_high_flag SVI_low_flag)
;

[Wolverine]

Thank you ballardw!  Using the code you provided as a guide, I was able to produce almost exactly the output I need.  Your explanation about exporting to Excel helped me to better understand the process, and the tip about listing multiple variables in the paired statement is especially helpful given how many comparison I have to run.

At this point, there is just 1 remaining issue: the output file is in alphabetical order by variable name, rather than the order that it should be in for the output table.  So going back to the sashelp.class data, what if I wanted Age-Weight and the then Age-Height?  I created a variable list in the proper order (a portion is shown below).  Is there a (relatively) easy way to specify that they should be in the same order as the var list?  Ideally, I'd like it to show

SVI_high_flag - max_FLAG_PersonalCancerHistory

SVI_high_flag - max_FLAG_Dxs_FamCa

SVI_low_flag - max_FLAG_PersonalCancerHistory

SVI_low_flag - max_FLAG_Dxs_FamCa

Etc, in that order. 

The only idea I have on how to do this is to take the list from the difference column, put it in Excel, and manually sort it into the correct order.  Then I would add a column with the correct sort order, import the Excel file, merge it to the "want" data file, and then sort to the correct order.  Obviously that would be tedious.  Any better solutions?

%let SVI_var_list = 
max_FLAG_PersonalCancerHistory
max_FLAG_Dxs_FamCa;


PROC TTEST plots=none
data=temp.SVI_hyst_output_SAS_dum;
paired SVI_high_flag * (&SVI_var_list.)
SVI_low_flag * (&SVI_var_list.)
SVI_SES_high_flag * (&SVI_var_list.)
SVI_SES_low_flag * (&SVI_var_list.)
SVI_HHcomp_high_flag * (&SVI_var_list.)
SVI_HHcomp_low_flag * (&SVI_var_list.);
RUN;

[Wolverine]

Rather than sorting every combination of variables, I ordered the variable1 list and variable2 list separately in an Excel file which was then imported in a SAS file called output_sort_order.  The final output file was then exported to Excel.  Here is the code that I used:

ods output conflimits=temp.conf
           ttests    =temp.ttest;
		   
PROC TTEST plots=none
			data=temp.SVI_hyst_output_SAS_dum;
	paired 	SVI_high_flag * (&SVI_var_list.)
			SVI_low_flag * (&SVI_var_list.)
			SVI_SES_high_flag * (&SVI_var_list.)
			SVI_SES_low_flag * (&SVI_var_list.)
			SVI_HHcomp_high_flag * (&SVI_var_list.)
			SVI_HHcomp_low_flag * (&SVI_var_list.);
RUN;

PROC SORT data=temp.conf;
   BY variable1 variable2;
RUN;

PROC SORT data=temp.ttest;
   BY variable1 variable2;
RUN;

DATA temp.SVI_hyst_ttest;
	MERGE temp.conf temp.ttest;
	BY difference;

	sig_level = "   ";
	IF probt >= .05 THEN sig_level = "NS";
	ELSE IF probt <= .05 AND probt > .01 THEN sig_level = "*";
	ELSE IF probt <= .01 AND probt > .001 THEN sig_level = "**";
	ELSE IF probt <= .001 THEN sig_level = "***";
RUN;

PROC SQL;
	Create table temp.SVI_hyst_ttest_output1
	as Select b.var1_sort, a.Variable1, a.Variable2, a.Difference, a.Mean,
			a.sig_level, a.LowerCLMean, a.UpperCLMean, a.StdDev, a.LowerCLStdDev, a.UpperCLStdDev,
			a.UMPULowerCLStdDev, a.UMPUUpperCLStdDev, a.tValue, a.DF, a.Probt
	From temp.SVI_hyst_ttest a LEFT JOIN temp.output_sort_order b
	On a.variable1 = b.variable1
	Order by var1_sort;
QUIT;

PROC SQL;
	Create table temp.SVI_hyst_ttest_output_final
	as Select a.var1_sort, b.var2_sort, a.Variable1, a.Variable2, a.Difference, a.Mean,
			a.sig_level, a.LowerCLMean, a.UpperCLMean, a.StdDev, a.LowerCLStdDev, a.UpperCLStdDev,
			a.UMPULowerCLStdDev, a.UMPUUpperCLStdDev, a.tValue, a.DF, a.Probt
	From temp.SVI_hyst_ttest_output1 a LEFT JOIN temp.output_sort_order b
	On a.variable2 = b.variable2
	Order by var1_sort, var2_sort;
QUIT;

[ballardw]

When there is no apparent natural order a tried-but-true method is to add a numeric order variable to the data, sort by that and use a procedure that will keep that order for display, such as Proc Print or possibly Report and Tabulate with the Order=data option in the right place.

One way that MAY work if you have a list in the correct order could involve using the FINDW function with your list of variable names and options to return word position instead of character position.

Or If/then/else statements in a data step using the names of the variables for each table.