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
Even if possible, SQL is not meant for row dependant calculations, a data step it's much easier to use for this scenario.
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;
I guess you need to use name instead of age in the order by and where clauses to get the intended result.
Kurt,
Please ignore my code. I think my code is not fit the OP's requirement.
@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;
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.