Calculating Aggregate Totals within a Data Set

[JaneNYC]

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.

[LinusH]

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.

[TomKari]

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

[JaneNYC]

Hi TomKari-

I need the vars only on the first row.

Thanks.

[TomKari]

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