topic Re: sum total each id in SAS Programming

sum total each id

BrahmanandaRao — Wed, 10 Feb 2021 12:07:40 GMT

data students;
input id sub1  sub2 sub3 sub4 sub5		;
datalines;
1 35 55 66 41 53
2	22 44 35 46 55
3	35 88 95 55 25
4	39 78 96 44 74
5	45 71 60 90 35
;
run;

proc means data=students sum maxdec=0;
class id;
var sub;
run;



proc tabulate data=mydata f=6. out=work.tabout;
class id;
var sub;
tables id, sub*(N sum);
run;



proc report data=students nowd out=work.repout;
column id sub1 sub2 sub2 sub3 sub4 sub5 ;
define id / group style(column)=Header;
define sub / n 'Count ';
define sub/ sum 'sub Sum';
run;

Sum the students marks with different procedures

Re: sum total each id

PaigeMiller — Wed, 10 Feb 2021 12:19:08 GMT

What is your question?

Re: sum total each id

PeterClemmensen — Wed, 10 Feb 2021 12:22:41 GMT

As with most problems, a long data set makes things much simpler

data students;
input id sub1-sub5;
datalines;
1 35 55 66 41 53
2 22 44 35 46 55
3 35 88 95 55 25
4 39 78 96 44 74
5 45 71 60 90 35
;

data long(drop = sub1-sub5);
   set students;
   array s sub:;
   do over s;
      sub = s;
      output;
   end;
run;

proc means data = long sum;
   class id;
   var sub;
run;

proc tabulate data=long;
   class id;
   var sub;
   tables id, sub=''*(N sum);
run;

proc report data = long;
    columns id sub;
    define id  / group;
    define sub / analysis sum;
run;

Re: sum total each id

Shmuel — Wed, 10 Feb 2021 12:36:45 GMT

There were errors in the code you posted:

1) For observation 2-5 there was an excess TAB character ('09'x) between the ID and sun1;

2) The input to proc tabulate should probably be students and not mydata.

3) To shorten list of variables replace sub1 sub2 ... by sub: - with the colon character. You missed the colon within all methods.

Next code is working:

data students;
infile datalines;
input id sub1  sub2 sub3 sub4 sub5		;
datalines;
1 35 55 66 41 53
2 22 44 35 46 55
3 35 88 95 55 25
4 39 78 96 44 74
5 45 71 60 90 35
;
run;

proc means data=students sum maxdec=0;
class id;
var sub:;
run;



proc tabulate data=students f=6. out=work.tabout;
class id;
var sub:;
tables id, (sub:)*(N sum);
run;



proc report data=students nowd out=work.repout;
column id sub1 sub2 sub2 sub3 sub4 sub5 ;
define id / group style(column)=Header;
define sub: / n 'Count ';
define sub:/ sum 'sub Sum';
run;

Re: sum total each id

BrahmanandaRao — Wed, 10 Feb 2021 14:43:47 GMT

I WANT EACH STUDENT ID SUBJECTS TOTAL (SUM) BUT ABOVE CODE WRONG

Re: sum total each id

PaigeMiller — Wed, 10 Feb 2021 15:31:11 GMT

@BrahmanandaRao wrote:
I WANT EACH STUDENT ID SUBJECTS TOTAL (SUM) BUT ABOVE CODE WRONG

What is wrong with it? You must know what is wrong, why don't you tell us?

Re: sum total each id

ballardw — Wed, 10 Feb 2021 15:31:55 GMT

@BrahmanandaRao wrote:
I WANT EACH STUDENT ID SUBJECTS TOTAL (SUM) BUT ABOVE CODE WRONG

If you want to Sub1+Sub2+Sub3+Sub4+Sub5 for each ID then none of the procedures is ideal though you can force report to do so.

data want;
   set students;
   total = sum(of sub:);
run;

Or reshape the data as @PeterClemmensen suggests.

And don't shout in all capital letters. You will get better responses if you SHOW what you expect a result to be.

Re: sum total each id

Shmuel — Wed, 10 Feb 2021 15:47:38 GMT

If I understand you correctly you want the horizontal sum of subject per row in a report

then check next codes:

(1) summing all SUBs in a row:

data students;
infile datalines;
input id sub1  sub2 sub3 sub4 sub5;
total = sum(of sub:);
datalines;
1 35 55 66 41 53
2 22 44 35 46 55
3 35 88 95 55 25
4 39 78 96 44 74
5 45 71 60 90 35
;
run;

2) Adding the total column in proc tabulate:

proc tabulate data=students f=6. out=work.tabout;
class id;
var sub: total;
tables id, ((sub:) total)*(N sum);
run;

3) Adding the total column in proc means:

proc means data=students sum maxdec=0;
class id;
var sub: total;
run;

4) Adding the total column in proc report:

proc report data=students nowd out=work.repout;
column id sub1 sub2 sub2 sub3 sub4 sub5 total;
define id / group style(column)=Header;
define sub: / sum "sub Sum";
define total/ sum 'Total Sum';
run;

I don't know if it is possible to show two different statistics of same variable(s).

If the out of above code is not what you want then post the format of the wanted report

Re: sum total each id

ballardw — Wed, 10 Feb 2021 21:30:44 GMT

@Shmuel wrote:

I don't know if it is possible to show two different statistics of same variable(s).

I am assuming the comment is for proc report.

proc report data=sashelp.class;
   columns sex height,(min mean max);
   define sex /group;
   define height/ analysis;
run;

is one way unless you had something more complex in mind for the multiple statistics.

Re: sum total each id

Shmuel — Thu, 11 Feb 2021 05:33:38 GMT

Thanks to @ballardw I can present here the proc report too:

proc report data=students nowd out=work.repout;
column id sub1 sub2 sub2 sub3 sub4 sub5 total, (N sum);
define id / group style(column)=Header;
define sub1 / display  ;
define sub2 / display ;
define sub3 / display ;
define sub4 / display ;
define sub5 / display ;
define total/ sum 'Total Sum';
run;