BookmarkSubscribeRSS Feed
JaneNYC
Calcite | Level 5

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;

codeQ1Q2A5A8A3TotalPoss1Totalmet1casescore1P18W30W15W88H38TotalPoss2Totalmet2casescore2
BD56711.004250%11.103267%
CN331.0014250%0.10122100%
S365111005360%011114375%
R555.01.13267%.01.122100%

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

The final product I'm looking for is this:

codeQ1Q2A5A8A3TotalPoss1Totalmet1casescore1AllPoss1AlltotalMet1AllScore1P18W30W15W88H38TotalPoss2Totalmet2casescore2AllPoss2AlltotalMet2AllScore2
BD56711.004250%16956%11.103267%11982%
CN331.0014250% 0.10122100%
S365111005360% 011114375%
R555.01.13267% .01.122100%

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

Thanks.

4 REPLIES 4
LinusH
Tourmaline | Level 20

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.

Data never sleeps
TomKari
Onyx | Level 15

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
Calcite | Level 5

Hi TomKari-

I need the vars only on the first row.

Thanks.

TomKari
Onyx | Level 15

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 709 views
  • 0 likes
  • 3 in conversation