dealing with duplicate records

[PCrider]

I have data containing student IDs, classes, grades, semesters, etc. I want to find all the times that a student repeated a class; so the same student ID and the same course number but not necessarily the same grade or semester. i want to keep only the last (by date) recorded grade for that class but at the same time i want to count the number of repeat classes.
here is some data:

ID@semester(1st number=semester number/last 2 numbers=year)
@class@credits@grade recieved

04F1M@191@C S [email protected]@UW@
04F1M@187 @CL CV [email protected]@C@
04F1M@587@ECON [email protected]@D-@
04F1M@590@ECON [email protected]@B@
04F1M@586@ENGL [email protected]@B@
04F1M@590@ENGL [email protected]@C-@

thanks

[art297]

You might be able to do what you want with something like:


data have;
infile cards delimiter="@";
informat class $10.;
format date date9.;
input ID $ semester $ class credits grade_recieved $;
date=mdy(substr(semester,1,1),1,substr(semester,2,2));
cards;
04F1M@191@C S [email protected]@UW
04F1M@187 @CL CV [email protected]@C
04F1M@587@ECON [email protected]@D-
04F1M@590@ECON [email protected]@B
04F1M@586@ENGL [email protected]@B
04F1M@590@ENGL [email protected]@C-
;

proc sort data=have;
by ID class date;
run;

data want;
set have;
by id class;
if first.class then count=1;
else count+1;
if last.class then output;
run;

HTH,
Art

[DBailey]

How about:

data Classes_Taken;
infile cards delimiter="@";
informat class $10.;
format date date9.;
input ID $ semester $ class credits grade_recieved $;
class_date=mdy(substr(semester,1,1),1,substr(semester,2,2));
cards;
04F1M@191@C S [email protected]@UW
04F1M@187 @CL CV [email protected]@C
04F1M@587@ECON [email protected]@D-
04F1M@590@ECON [email protected]@B
04F1M@586@ENGL [email protected]@B
04F1M@590@ENGL [email protected]@C-
;
run;

proc sql;
create table retakes as
select
id,
class,
count(*) as times_taken,
max(class_date) Last_date
from work.classes_taken
group by id, class
having count(*)>1;

create table last_retake as
select
t2.*,
t1.times_taken
from work.retakes t1 inner join work.classes_taken t2
on t1.id=t2.id and t1.class=t2.class and t1.last_date=t2.class_date;
quit;