Duplicates left after using Distinct

[irinnew]

Hi everybody!

My purpose is to extract record for each member with the most recent date from

basemulti

1 st step : I extracted the 1st query and got 15473 records

2nd step: I joined newly created table MaxDateOnly_multi to the basemulti table JUST

IN ORDER TO GET ALL FIELDS from basemulti table ....and get unexpected number of

records while they supposed to be equal.
I reviewed the result table MultiWithMaxDate and found out that I got some duplicates

while assumed that distinct would eliminate them.

What I am doing wrong?

Below is what my log told


289 /*just to establish most recent date through Aggregate function*/
290 PROC SQL;
291 create table MaxDateOnly_multi as
292 (select mem_Id,
293 max(mem_Day) as mem_day_
294 from basemulti
295 Group By lr_Hedis_Id
296 );
NOTE: Table WORK.MAXDATEONLY_MULTI created, with 15473 rows and 2 columns.

297
NOTE: PROCEDURE SQL used:
real time 2.51 seconds
cpu time 0.13 seconds


298 proc sql;
299 create table MultiWithMaxDate as
300 select distinct dd.*
301 from basemulti dd
302 join MaxDateOnly_multi mm
303 on dd.mem_id=mm.mem_id
304 where dd.mem_day=mm.mem_day_;


NOTE: Table WORK.MULTIWITHMAXDATE created, with 16007 rows and 10 columns.


Thank you very much in advance

Irin

 

[klaz2002]

Irin,
Perhaps the second ds has more records than your first dataset.

Try proc sort with the nodupkey and check the number of obs on each dataset.

 

[deviate93]

Could you have multiple records in basemulti with the same mem_id and mem_day?

Easy thing to do is to sort MultiWithMaxDate by mem_day and create a dataset that has the duplicates (data dup;set multiwithmaxdate;by mem_day;if first.mem_day and last.mem_day then delete then look at the duplicates.