How to add scores if the exam dates are same for the combination of three or more variables

[wanttolearnSAS]

StudentId  Addpdr Subject GradeC ExamDate    Marks

001             IN         PHY       X1      22/03/2017     73

001             PK        CHE      X11     02/03/2016     82

001             IN         PHY       X1      22/03/2017     62

001             IN         PHY       X1      10/11/2016     87

001             IN         PHY       X1      23/03/2017     92

001             PK        CHE       X11    10/03/2017     51

002             BN        PHY       X1      27/03/2017     62

002             BN        PHY       X11    02/03/2017     98

002             BN        PHY       X1      27/03/2017     34

002             BN        PHY       X1      27/03/2017     53

The desired output is:

StudentId  Addpdr  GradeC  Subject   Marks

001            IN           X1          PHY        135

001            PK         X11         CHE         51

002            BN         X1          PHY        149

002            BN        X11         PHY          98

The Marks in the output is the addition of the marks if the latest exam date (with the combination of Studentid,Addpdr and GradeC) is repeating else the marks is the marks of the latest exam date for the combination of Studentid,Addpdr and GradeC.

[Reeza]

What have you tried so far?

I would suggest using PROC MEANS to summarize along the grouping variables you've identified and then to use PROC SORT to take the latest record using the NODUPKEY option. If you post what you've tried so far we can help fix/modify your code as necessary.

---

@wanttolearnSAS wrote:

StudentId  Addpdr Subject GradeC ExamDate    Marks

001             IN         PHY       X1      22/03/2017     73

001             PK        CHE      X11     02/03/2016     82

001             IN         PHY       X1      22/03/2017     62

001             IN         PHY       X1      10/11/2016     87

001             IN         PHY       X1      23/03/2017     92

001             PK        CHE       X11    10/03/2017     51

002             BN        PHY       X1      27/03/2017     62

002             BN        PHY       X11    02/03/2017     98

002             BN        PHY       X1      27/03/2017     34

002             BN        PHY       X1      27/03/2017     53

The desired output is:

StudentId  Addpdr  GradeC  Subject   Marks

001            IN           X1          PHY        135

001            PK         X11         CHE         51

002            BN         X1          PHY        149

002            BN        X11         PHY          98

The Marks in the output is the addition of the marks if the latest exam date (with the combination of Studentid,Addpdr and GradeC) is repeating else the marks is the marks of the latest exam date for the combination of Studentid,Addpdr and GradeC.

---

[art297]

Here is one way:

data have;
  input (StudentId  Addpdr Subject GradeC) ($) ExamDate :ddmmyy10.  Marks;
  cards;
001             IN         PHY       X1      22/03/2017     73
001             PK        CHE      X11     02/03/2016     82
001             IN         PHY       X1      22/03/2017     62
001             IN         PHY       X1      10/11/2016     87
001             IN         PHY       X1      23/03/2017     92
001             PK        CHE       X11    10/03/2017     51
002             BN        PHY       X1      27/03/2017     62
002             BN        PHY       X11    02/03/2017     98
002             BN        PHY       X1      27/03/2017     34
002             BN        PHY       X1      27/03/2017     53
;

proc sort data=have;
  by StudentID Addpdr GradeC descending ExamDate;
run;

proc summary data=have;
  var Marks;
  by  StudentID Addpdr GradeC descending ExamDate;
  output out=need (rename=(_freq_=count )
                   drop=_type_ ExamDate
                   where=(count ge 2)) sum=sum_m;
run;

data want (drop=count sum_m ExamDate);
  merge have need;
  by StudentID Addpdr GradeC;
  if first.GradeC then do;
    if count ge 2 then Marks=sum_m;
    output;
  end;
run;

Art, CEO, AnalystFinder.com

[wanttolearnSAS]

I have sorted and summarized and then merged.

It is working.

Thanks a lot.