topic Re: How to add scores if the exam dates are same for the combination of three or more variables in SAS Data Management

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

wanttolearnSAS — Fri, 21 Jul 2017 20:11:23 GMT

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.

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

Reeza — Fri, 21 Jul 2017 23:48:34 GMT

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.

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

art297 — Fri, 21 Jul 2017 23:54:24 GMT

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

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

wanttolearnSAS — Sat, 22 Jul 2017 05:52:24 GMT

I have sorted and summarized and then merged.

It is working.

Thanks a lot.