Using values from different ODS output data sets to perform a calculation

[abaker_ca]

Hello,

I'm trying to use the output from a PROC NESTED procedure to calculate and display variance components, % of total variance for each component, and the %CV. Ideally, I'd like all of these statistics stored in one final data set in a horizontal line (see end of post for example). 

However, I would settle for just being able to calculate the values programmatically and printing out a few different data sets to have the statistics viewable in a reasonably easy to read format.  I have already figured out how to use ods trace to record the values I need from PROC NESTED into data frames.  For the variance components and % total variance for each component, it's easy to calculate those values from one of the ods output data sets. I've shown the code I wrote to do this below. 

Unfortunately, one of the values I need to calculate for my report is the %CV. This relies on values in two separate ods output tables ('anova' and 'statistics'). I need to divide the total variability in the form of a standard deviation (reported in 'anova' ods table) by the grand mean (reported in 'statistics' ods table) to get the %CV. I don't want to manually calculate the %CV because I will be performing this whole procedure about 50 times and I'd like to turn it into a macro. 

Here is an example data set and the code I have written to calculate variance components (in SD) and %Variance for each component.

/*input data*/
title1 'Reliability of Automobile Models';
data auto;
input Make $ Model Score @@;
datalines;
a 1 62 a 2 77 a 3 59
a 1 67 a 2 73 a 3 64
a 1 60 a 2 79 a 3 60
b 1 72 b 2 58 b 3 80
b 1 75 b 2 63 b 3 84
b 1 69 b 2 57 b 3 89
c 1 94 c 2 76 c 3 81
c 1 90 c 2 75 c 3 85
c 1 88 c 2 78 c 3 85
d 1 69 d 2 73 d 3 90
d 1 72 d 2 88 d 3 87
d 1 76 d 2 87 d 3 92
;

/*sort data to prepare for proc nested*/

proc sort data=auto;
by Make Model;
run;

/*turn on ods trace and generate a data set with the variance*/

ods output anova = sd_values;

/*call proc nested*/
proc nested data=auto;
class Make Model;
var Score;
run;
ods output close;

/*close trace*/

/*turn on ods trace and generate a data set with the grand mean*/

ods output statistics = grand_mean;

/*call proc nested*/

proc nested data=auto;
class Make Model;
var Score;
run;
ods output close;

/*close trace*/

/*calculate variance components in sds and calculate % total variance for each component*/
data anova_1;
set sd_values;

sd_value = round(sqrt(VarComp), 0.1);
pct_value = round(percent, 0.1);

run;

/*sort by source to get ready for data transposition*/

proc sort data = anova_1;
by source;
run;

/*transpose data so all values are in one column*/

proc transpose data = anova_1 out = anova_1t;
var sd_value pct_value;
by source;
run;

/*print out values from data set*/

proc print data = anova_1t;

run;

What's remaining is calculating the %CV and ideally appending both the grand mean and the %CV to the table I printed in the last step of my code.  

Is there a way to calculate the %CV programmatically using the ods tables? Or is this best accomplished some other way?

Also, if anyone can give me tips on how to get 'anova_1t' back into a horizontal format with all values reported on 1 line, that would be very useful. See the attached screenshot for an example. The column headers don't need to be formatted perfectly. It's more important that the values are in the correct order and reported on 1 line.

Mean score	Make SD	Make %Total	Model SD	Model %Total	Error SD	Error %Total	Total SD	%CV
75.9	5.8	25.7	9.1	63.3	3.8	11.1	11.5	15.2

[PaigeMiller]

You can merge the ODS output data sets into one using a SAS data step, so you can do calculations.

If you want ANOVA_1T to be horizontal, use PROC TRANSPOSE.

[Reeza]

This should get you closer. One thing to note is that you can have multiple ODS tables saved from one proc call, no need to run that step multiple times. 

title1 'Reliability of Automobile Models';

data auto;
	input Make $ Model Score @@;
	datalines;
a 1 62 a 2 77 a 3 59
a 1 67 a 2 73 a 3 64
a 1 60 a 2 79 a 3 60
b 1 72 b 2 58 b 3 80
b 1 75 b 2 63 b 3 84
b 1 69 b 2 57 b 3 89
c 1 94 c 2 76 c 3 81
c 1 90 c 2 75 c 3 85
c 1 88 c 2 78 c 3 85
d 1 69 d 2 73 d 3 90
d 1 72 d 2 88 d 3 87
d 1 76 d 2 87 d 3 92
;

	/*sort data to prepare for proc nested*/
proc sort data=auto;
	by Make Model;
run;

ods output anova=sd_values statistics=grand_mean;

proc nested data=auto;
	class Make Model;
	var Score;
run;

/*calculate variance components in sds and calculate % total variance for each component*/
data anova_1;
	set sd_values;
	sd_value=round(sqrt(VarComp), 0.1);
	pct_value=round(percent, 0.1);
run;

/*sort by source to get ready for data transposition*/
proc sort data=anova_1;
	by source;
run;

/*transpose data so all values are in one column*/
proc transpose data=anova_1 out=anova_1t;
	var sd_value pct_value;
	by source;
run;

*transpose to desired format;

proc transpose data=anova_1t out=wide_data delim=_;
	id source _name_;
run;

*merge results in to one data set;

data want;
	merge grand_mean (where=(statistic='Score Mean') keep=statistic nvalue1 
		rename=nvalue1=GrandMean) wide_data;
	drop statistic _name_;
run;

*display for checking;

proc print data=want;
run;

Results (too lazy to format for viewing, sorry)

Obs	GrandMean	Error_sd_value	Error_pct_value	Make_sd_value	Make_pct_value	Model_sd_value	Model_pct_value	Total_sd_value	Total_pct_value
1	75.944444	3.8	11.1	5.8	25.7	9.1	63.3	11.5	100

Spoiler

---

@abaker_ca wrote:

Hello,

I'm trying to use the output from a PROC NESTED procedure to calculate and display variance components, % of total variance for each component, and the %CV. Ideally, I'd like all of these statistics stored in one final data set in a horizontal line (see end of post for example). 

However, I would settle for just being able to calculate the values programmatically and printing out a few different data sets to have the statistics viewable in a reasonably easy to read format.  I have already figured out how to use ods trace to record the values I need from PROC NESTED into data frames.  For the variance components and % total variance for each component, it's easy to calculate those values from one of the ods output data sets. I've shown the code I wrote to do this below. 

Unfortunately, one of the values I need to calculate for my report is the %CV. This relies on values in two separate ods output tables ('anova' and 'statistics'). I need to divide the total variability in the form of a standard deviation (reported in 'anova' ods table) by the grand mean (reported in 'statistics' ods table) to get the %CV. I don't want to manually calculate the %CV because I will be performing this whole procedure about 50 times and I'd like to turn it into a macro. 

Here is an example data set and the code I have written to calculate variance components (in SD) and %Variance for each component.

/*input data*/
title1 'Reliability of Automobile Models';
data auto;
input Make $ Model Score @@;
datalines;
a 1 62 a 2 77 a 3 59
a 1 67 a 2 73 a 3 64
a 1 60 a 2 79 a 3 60
b 1 72 b 2 58 b 3 80
b 1 75 b 2 63 b 3 84
b 1 69 b 2 57 b 3 89
c 1 94 c 2 76 c 3 81
c 1 90 c 2 75 c 3 85
c 1 88 c 2 78 c 3 85
d 1 69 d 2 73 d 3 90
d 1 72 d 2 88 d 3 87
d 1 76 d 2 87 d 3 92
;

/*sort data to prepare for proc nested*/

proc sort data=auto;
by Make Model;
run;

/*turn on ods trace and generate a data set with the variance*/

ods output anova = sd_values;

/*call proc nested*/
proc nested data=auto;
class Make Model;
var Score;
run;
ods output close;

/*close trace*/

/*turn on ods trace and generate a data set with the grand mean*/

ods output statistics = grand_mean;

/*call proc nested*/

proc nested data=auto;
class Make Model;
var Score;
run;
ods output close;

/*close trace*/

/*calculate variance components in sds and calculate % total variance for each component*/
data anova_1;
set sd_values;

sd_value = round(sqrt(VarComp), 0.1);
pct_value = round(percent, 0.1);

run;

/*sort by source to get ready for data transposition*/

proc sort data = anova_1;
by source;
run;

/*transpose data so all values are in one column*/

proc transpose data = anova_1 out = anova_1t;
var sd_value pct_value;
by source;
run;

/*print out values from data set*/

proc print data = anova_1t;

run;

What's remaining is calculating the %CV and ideally appending both the grand mean and the %CV to the table I printed in the last step of my code.  

Is there a way to calculate the %CV programmatically using the ods tables? Or is this best accomplished some other way?

Also, if anyone can give me tips on how to get 'anova_1t' back into a horizontal format with all values reported on 1 line, that would be very useful. See the attached screenshot for an example. The column headers don't need to be formatted perfectly. It's more important that the values are in the correct order and reported on 1 line.

Mean score	Make SD	Make %Total	Model SD	Model %Total	Error SD	Error %Total	Total SD	%CV
75.9	5.8	25.7	9.1	63.3	3.8	11.1	11.5	15.2

---