SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lhsumdalum
Obsidian | Level 7

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:

 ResetCounterByGroup.PNG

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

 

View solution in original post

3 REPLIES 3
gamotte
Rhodochrosite | Level 12

Hello,

 

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

 

if first.student_ID then do; ....

Reeza
Super User

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:

 ResetCounterByGroup.PNG

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
Opal | Level 21

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

 

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 2311 views
  • 1 like
  • 4 in conversation