topic Re: Cumulative variable in SQL in SAS Procedures

Cumulative variable in SQL

PCrider — Thu, 14 Apr 2011 06:50:34 GMT

I'm working with data concerning grades by semester and year. I have been able to calculate the GPA for each individual semester as well as the overall GPA (by revising my group by statement) . Now what i want is a cumulative variable that will show what the current GPA is after any given semester. For example, if someone had this:
semester 1 GPA = 3.7, semester 2 GPA = 3.1, semester 3 GPA = 2.8
then the cumulative variable would be:
semseter 1 CumGPA = 3.7, semester 2 CumGPA = 3.4, semester 3 CumGPA = 3.2.
here is some of my code:

proc sql; /*GPA per semester*/
create table A as
select netid, semester, year, sum(credits*gradepoint)/sum(credits) as gpa
from grades
group by netid, semester, year;
quit;

proc sql; /*overall GPA*/
create table B as
select netid, sum(credits*gradepoint)/sum(credits) as OverallGPA
from grades
group by netid;
quit;

Thanks for your help

Re: Cumulative variable in SQL

Ksharp — Thu, 14 Apr 2011 09:47:16 GMT

Can you explain how to yield this cumulative variable?
What is your logic ?

Ksharp

Re: Cumulative variable in SQL

DBailey — Thu, 14 Apr 2011 12:02:40 GMT

proc sql;
create table C as
select
t1.netid,
t1.year,
t1.semester,
sum(t2.credits*t2.gradepoint)/sum(t2.credits) as Cumulative_GPA
from
grades t1
left outer join grades t2
on t1.netid=t2.netid and t1.year=t2.year
where
t2.semester le t1.semester
group by
t1.netid, t1.year, t1.semester;
quit;