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

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

SAS_learneromg — Fri, 24 Sep 2021 16:24:23 GMT

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?

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

PaigeMiller — Fri, 24 Sep 2021 16:30:06 GMT

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;

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

acordes — Fri, 24 Sep 2021 16:34:26 GMT

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;

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

SAS_learneromg — Fri, 24 Sep 2021 16:42:03 GMT

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