What to clean up a set of test scores. How would I do this using SQL?

[SAS_learneromg]

I have a dataset of test scores. My variables are (test taker’s) ID_no, test date, test score, and test type (online or in person).

Some people took the test online and in person, and therefore, are in the dataset more than once.

I’d like to make a new dataset with only those whose ID_no shows up more than once. And then with that dataset, I’d like to clean it to only include the most recent test date for each ID_no.

I'm not super familiar with SQL and want to develop this skill. What kind of procedure would I need to use to achieve this?

[PaigeMiller · Accepted solution]

Assuming that the people who took the test online and in person did not do this both on the same day, this should work

proc sql;
     create table want as select * from have
     group by ID_no
     having test_date=max(test_date);
quit;

[SAS_learneromg]

Yes, that's exactly what I needed to. Thank you!

[acordes]

Written on my smartphone here comes a possible solution that you could try.
Proc Sort data=have;
By id_no test_date;
Run;

Data want;
Set have;
By id_no test_date;
If first.id_no <> last.id_no;
Call missing(test_score);
Run;