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;
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)
;
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;
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;
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)
;
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;
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: