how to do age cumulative sum in sashelp.class in sql

[thanikondharish]

Name	Sex	Age
Alfred	M	14
Alice	F	13
Barbara	F	13
Carol	F	14
Henry	M	14
James	M	12
Jane	F	12
Janet	F	15
Jeffrey	M	13
John	M	12
Joyce	F	11
Judy	F	14
Louise	F	12
Mary	F	15
Philip	M	16
Robert	M	12
Ronald	M	15
Thomas	M	11
William	M	15

The above dataset is sashelp.class dataset now I want to do age cumulative sum by sex wise in sql 

I did in datastep block but how to do in sql 

like see below dataset 

Name	Sex	Age	cum_age
Alice	F	13	13
Barbara	F	13	26
Carol	F	14	40
Jane	F	12	52
Janet	F	15	67
Joyce	F	11	78
Judy	F	14	92
Louise	F	12	104
Mary	F	15	119
Alfred	M	14	14
Henry	M	14	28
James	M	12	40
Jeffrey	M	13	53
John	M	12	65
Philip	M	16	81
Robert	M	12	93
Ronald	M	15	108
Thomas	M	11	119
William	M	15	134

t

[LinusH]

Even if possible, SQL is not meant for row dependant calculations, a data step it's much easier to use for this scenario.

[Ksharp]

Yes. PROC SQL is not right way to do it .

proc sql;
create table want as
 select *,(select sum(age) from sashelp.class where sex=a.sex and age le a.age) as cum_age
  from sashelp.class as a
   order by sex,age;
quit;

[Kurt_Bremser]

I guess you need to use name instead of age in the order by and where clauses to get the intended result.

[Ksharp]

Kurt,

Please ignore my code. I think my code is not fit the OP's requirement.

[BrahmanandaRao]

data agesum;
set sashelp.class;
ages=sum(of age);
proc print;
run;

Here I want sum of all ages how to write a code datastep

[Kurt_Bremser]

All you need is a Sum Statement (look up the documentation):

ages + age;

[BrahmanandaRao]

data agesum;
set sashelp.class;
ages=sum(of age);
proc print;
run;

How many ways summ all ages

[ballardw]

---

@BrahmanandaRao wrote:
data agesum;
set sashelp.class;
ages=sum(of age);
proc print;
run;

How many ways summ all ages

---

---

@BrahmanandaRao wrote:
data agesum;
set sashelp.class;
ages=sum(of age);
proc print;
run;

How many ways summ all ages

---

Proc means/ summary, reporting procedures like Report and Tabulate, Proc Print with the sum option, Proc Sql with a select sum(age) all available with the foundation SAS packages. Other procedures could do this as well.

Data step would be to do something like the following to do a running total:

data want;
   set sashelp.class;
   retain agetotal;
   agetotal= sum(age,agetotal);
run;

[Reeza]

That code will not sum the variable ages.

To answer your question, there are at least 9 ways I can think of to sum a variable in SAS off the top of my head.

---

@BrahmanandaRao wrote:
data agesum;
set sashelp.class;
ages=sum(of age);
proc print;
run;

How many ways summ all ages

---

[PGStats]

If you *really* have to do this using SQL, I would favor:

proc sql;
select 
    a.name,
    a.sex,
    a.age,
    sum(b.age) as cumAgeSum
from
    sashelp.class as a left join
    sashelp.class as b on a.sex=b.sex and a.name ge b.name
group by a.sex, a.name, a.age;
quit;

wouldn't quite work if there existed two students with same name, age and sex.