How to Reset a Counter Variable/Calculation Function by ID/Group

[lhsumdalum]

Hi, 

I am trying to reset a counter variable/calculation function by Student ID in the following example. 

DATA GPAStats (KEEP = TCreditHrs TGradePts GPA STUDENT_ID); 
	SET test.analysts_grades; 
	BY STUDENT_ID; 
	RETAIN TCreditHrs 0 TGradePts 0 GPA 0; 
	
	SELECT(GRADE); 
		WHEN('A') GradePts = 4; 
		WHEN('B') GradePts = 3; 
		WHEN('C') GradePts = 2; 
		WHEN('D') GradePts = 1; 
		OTHERWISE GradePts = .; 
	END; 
	
		TCreditHrs = TCreditHrs + CREDIT_HR; 
		TGradePts = TGradePts + (GradePts * CREDIT_HR); 
		GPA = TGradePts / TCreditHrs; 

RUN; 

Here is how the data displays:

 

As you can see, the functions TCreditHrs, TGradePts and GPA continue to calculate for each Student ID... I would like the functions to reset at the beginning of each Student ID group. 

Further, I would like to figure out how to select just the last row for each student so that I select just the cumulative total credits, total grade pts, and gpa for all classes. 

Thanks in advance for all the help. 

[gamotte]

Hello,

The "by XXX" statement creates two automatic variables "first.XXX and last.XXX" :

if first.student_ID then do; ....

[Reeza]

You can use the FIRST/LAST logic, like below. Make sure to put it before you do the sums. 

if first.studentID then do;
tcreditHrs=0;
...


end;

If you use the SUM function instead of + you can set them to MISSING instead using CALL MISSING.

if first.studentID then call missing(tcredithrs, tgradepts, gpa);

tcredithrs = sum(tcredithrs, credit_hr)

SUM() considers missing the equivalent of 0, whereas + with a missing value will return a missing value. 

To keep only the last record, use the LAST, opposite of FIRST.

if last.studentID;

---

@lhsumdalum wrote:

Hi, 

I am trying to reset a counter variable/calculation function by Student ID in the following example. 

DATA GPAStats (KEEP = TCreditHrs TGradePts GPA STUDENT_ID); 
	SET test.analysts_grades; 
	BY STUDENT_ID; 
	RETAIN TCreditHrs 0 TGradePts 0 GPA 0; 
	
	SELECT(GRADE); 
		WHEN('A') GradePts = 4; 
		WHEN('B') GradePts = 3; 
		WHEN('C') GradePts = 2; 
		WHEN('D') GradePts = 1; 
		OTHERWISE GradePts = .; 
	END; 
	
		TCreditHrs = TCreditHrs + CREDIT_HR; 
		TGradePts = TGradePts + (GradePts * CREDIT_HR); 
		GPA = TGradePts / TCreditHrs; 

RUN; 

Here is how the data displays:

 

As you can see, the functions TCreditHrs, TGradePts and GPA continue to calculate for each Student ID... I would like the functions to reset at the beginning of each Student ID group. 

Further, I would like to figure out how to select just the last row for each student so that I select just the cumulative total credits, total grade pts, and gpa for all classes. 

Thanks in advance for all the help. 

---

[art297 · Accepted solution]

In addition to the suggestions you have already received, I think you may want to give the problem a little more thought.

For example, you don't include F in your grade format. Shouldn't it get a value of 0 rather than a missing value?

And are all grades counted in the calculation of the GPA? Don't some/most schools omit grades like pass, no pass, withdraw, transfer and incomplete?

As such, I think you might want something closer to:

DATA GPAStats (KEEP = TCreditHrs TGradePts GPA STUDENT_ID); 
  SET test.analysts_grades; 
  BY STUDENT_ID; 
  RETAIN TGPACreditHrs 0 TCreditHrs 0 TGradePts 0 GPA 0; 
	
  SELECT(GRADE); 
    WHEN('A') GradePts = 4; 
    WHEN('B') GradePts = 3; 
    WHEN('C') GradePts = 2; 
    WHEN('D') GradePts = 1; 
    WHEN('F') GradePts = 0; 
    OTHERWISE GradePts = .; 
  END; 

  if first.STUDENT_ID then do;
    call missing(TCreditHrs);
    call missing(TGradePts);
    call missing(TGPACreditHrs);
  end;

  TCreditHrs+CREDIT_HR;

  if not missing(GradePts) then do;
    TGPACreditHrs+CREDIT_HR; 
    TGradePts+ (GradePts * CREDIT_HR);
  end;
  
  if last.STUDENT_ID then do;
    GPA = TGradePts / TGPACreditHrs;
    output;
  end;
RUN;

Art, CEO, AnalystFinder.com