topic Calculating Aggregate Totals within a Data Set in SAS Procedures

Calculating Aggregate Totals within a Data Set

JaneNYC — Wed, 05 Dec 2012 14:13:17 GMT

Hello,

I'm trying to calculate some final scores for survey data using base SAS. I need to added summery totals at the end of sections.

My code right now is this

data want (keep=

code Q1 Q2 A5 A8 TotalPoss1 Totalmet1 casescore1

P18 W30 W15 W88 H38 TotalPoss2 Totalmet2 casescore2;

set CCR_Scoredt;

TotalPoss1= n(Q1 Q2 A5 A8);

Totalmet1= sum(Q1 Q2 A5 A8);

casescore1= Totalmet1/ TotalPoss1;

TotalPoss2= n(P18 W30 W15 W88);

Totalmet2= sum(P18 W30 W15 W88);

Casescore2= Totalmet2/ TotalPoss2;

format casescore1 Casescore2 Percent6.

run;

code	Q1	Q2	A5	A8	A3	TotalPoss1	Totalmet1	casescore1	P18	W30	W15	W88	H38	TotalPoss2	Totalmet2	casescore2
BD567	1	1	.	0	0	4	2	50%	1	1	.	1	0	3	2	67%
CN33	1	.	0	0	1	4	2	50%	0	.	1	0	1	2	2	100%
S365	1	1	1	0	0	5	3	60%	0	1	1	1	1	4	3	75%
R555	.	0	1	.	1	3	2	67%	.	0	1	.	1	2	2	100%

I need to add in additional variables which give aggregate totals.

The final product I'm looking for is this:

code	Q1	Q2	A5	A8	A3	TotalPoss1	Totalmet1	casescore1	AllPoss1	AlltotalMet1	AllScore1	P18	W30	W15	W88	H38	TotalPoss2	Totalmet2	casescore2	AllPoss2	AlltotalMet2	AllScore2
BD567	1	1	.	0	0	4	2	50%	16	9	56%	1	1	.	1	0	3	2	67%	11	9	82%
CN33	1	.	0	0	1	4	2	50%				0	.	1	0	1	2	2	100%
S365	1	1	1	0	0	5	3	60%				0	1	1	1	1	4	3	75%
R555	.	0	1	.	1	3	2	67%				.	0	1	.	1	2	2	100%

I think I should use by group processing but I’m having trouble figuring it out. Your feedback is appreciated.

Thanks.

Re: Calculating Aggregate Totals within a Data Set

LinusH — Wed, 05 Dec 2012 15:35:46 GMT

Storing totals/subtotals in tables is not usually best practice.

You may want to explore Proc Report, which is a quite flexible tool for doing different kind of calculations.

Re: Calculating Aggregate Totals within a Data Set

TomKari — Wed, 05 Dec 2012 15:38:01 GMT

So it looks to me like AllPoss1 is the sum for all rows of TotalPos1, and AlltotalMet1 is the sum of all rows of TotalMet1, and AllScore1 is just AlltotalMet1 / AllPoss1.

And the same for the ...2 variables.

For these six variables, do you want them on every row of your data, or only on the first row?

Tom

Re: Calculating Aggregate Totals within a Data Set

JaneNYC — Wed, 05 Dec 2012 16:10:54 GMT

Hi TomKari-

I need the vars only on the first row.

Thanks.

Re: Calculating Aggregate Totals within a Data Set

TomKari — Wed, 05 Dec 2012 17:18:55 GMT

This step will create a 1-record table with your totals:

PROC SQL;

CREATE TABLE want_totals AS

SELECT (SUM(TotalPoss1)) AS AllPoss1,

(SUM(Totalmet1)) AS AlltotalMet1,

(SUM(Totalmet1) / SUM(TotalPoss1)) FORMAT=Percent6. AS AllScore1,

(SUM(TotalPoss2)) AS AllPoss2,

(SUM(Totalmet2)) AS AlltotalMet2,

(SUM(Totalmet2) / SUM(TotalPoss2)) FORMAT=Percent6. AS AllScore2

FROM WANT;

QUIT;

And then this will merge your totals back into your original dataset:

data want_with_totals;

merge want want_totals;

run;

Tom