topic Getting one record from near duplicate records in SAS Procedures

Getting one record from near duplicate records

deleted_user — Mon, 27 Oct 2008 09:12:11 GMT

Hi All

I have a dataset that has near duplicate records - not exactly duplicates - take for example

1234 PHay ABSS EFGH $20524 20Jun2008
1234 PHay ABCD EFGH $11000 25Jun2008

Now, what I am trying to do is take only the latest record by date (in this case I want to retreive only the second field) - how can I acheive this?

I tried the Max() SQL Function, but then I need to use a function on all of the columns, as I need to get all the columns.

If I used MAX(Date), MAX(Salary), etc I would get back the first records' $20524 value, which is not correct.

Any help much appreciated.

Thanks!! Message was edited by: sdcruz

Re: Getting one record from near duplicate records

LinusH — Mon, 27 Oct 2008 11:10:32 GMT

If you want to use SQL, here's on solution:

26 proc sql;
27 select id, name, str1, str2, salary, date
28 from salary
29 group by id
30 having date = max(date);
NOTE: The query requires remerging summary statistics back with the original data.
31 quit;

As you see, this creates one summary query that is merged with original query, so this might be the best method if your data is huge. Other methods could involve two subsequent PROC SORT, the second one using NODUPKEY on your id column.

/Linus

Re: Getting one record from near duplicate records

Patrick — Mon, 27 Oct 2008 11:45:59 GMT

That should do the job:

proc sort data=yourdata;
by id date;
run;

data want;
set yourdata;
by id date;
if last.id then output;
run;

Re: Getting one record from near duplicate records

deleted_user — Tue, 28 Oct 2008 05:21:20 GMT

Hi Guys

Thanks alot for your help - yes I resolved this by using the PROC SQL procedure as outlined by Linus.

Thanks again - much appreciated!

Cheers
Shelton.