topic Re: how to do age cumulative sum in sashelp.class in sql in SAS Programming

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

thanikondharish — Sun, 30 Dec 2018 06:57:28 GMT

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

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

LinusH — Sun, 30 Dec 2018 09:51:01 GMT

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

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

Ksharp — Sun, 30 Dec 2018 10:31:50 GMT

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;

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

Kurt_Bremser — Sun, 30 Dec 2018 12:31:20 GMT

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

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

PGStats — Sun, 30 Dec 2018 22:17:24 GMT

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.

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

Ksharp — Tue, 01 Jan 2019 11:13:20 GMT

Kurt,

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

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

BrahmanandaRao — Wed, 14 Oct 2020 05:32:03 GMT

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

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

Kurt_Bremser — Wed, 14 Oct 2020 11:45:55 GMT

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

ages + age;

Sum age variablr

BrahmanandaRao — Wed, 14 Oct 2020 17:00:11 GMT

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

How many ways summ all ages

Re: Sum age variablr

ballardw — Wed, 14 Oct 2020 18:06:21 GMT

@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;

Re: Sum age variablr

Reeza — Wed, 14 Oct 2020 17:59:20 GMT

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