Help using Base SAS procedures

Cumulative variable in SQL

Reply
New Contributor
Posts: 4

Cumulative variable in SQL

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
Super User
Posts: 9,691

Re: Cumulative variable in SQL

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

Ksharp
Super Contributor
Posts: 578

Re: Cumulative variable in SQL

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;
Ask a Question
Discussion stats
  • 2 replies
  • 426 views
  • 0 likes
  • 3 in conversation