BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
3 REPLIES 3
LinusH
Tourmaline | Level 20
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
Data never sleeps
Patrick
Opal | Level 21
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;
deleted_user
Not applicable
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 600 views
  • 0 likes
  • 3 in conversation