DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

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

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. 


Accepted Solutions
Solution
‎02-12-2018 03:40 PM
PROC Star
Posts: 8,114

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

Posted in reply to lhsumdalum

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


All Replies
Super Contributor
Posts: 320

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

Posted in reply to lhsumdalum

Hello,

 

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

 

if first.student_ID then do; ....

Super User
Posts: 22,850

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

[ Edited ]
Posted in reply to lhsumdalum

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. 


 

Solution
‎02-12-2018 03:40 PM
PROC Star
Posts: 8,114

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

Posted in reply to lhsumdalum

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

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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