topic Re: Finding redundant entries for same ID and date in SAS Data Management

Finding redundant entries for same ID and date

mcdj — Thu, 11 Feb 2016 09:52:28 GMT

Hi there,I have longitudinal data in long format. each individual (part of a couple) is supposed to have a single row per visit. there are some, like below, that have 2 entries for an individual on the same date. I need to find out which IDs and for which visitdates there are multiple rows/entries. Help? Thanks!

Obs	id	htid	womanage	manage	cjbase	visitdate	linked	inflam_3mo_female
1244	5538M	5538	22	35	+-	05NOV2003	1	.
1245	5538F	5538	22	35	+-	05NOV2003	1	1
1246	5538F	5538	22	35	+-	05NOV2003	1

Re: Finding redundant entries for same ID and date

mohamed_zaki — Thu, 11 Feb 2016 10:56:04 GMT

proc sort data=have out=havesorted;
by id visitdate;
run;
data want ;
set havesorted ;
by id visitdate;
if not (first.visitdate and last.visitdate) then output;
run;

Re: Finding redundant entries for same ID and date

LinusH — Thu, 11 Feb 2016 11:42:02 GMT

If you wish to output all observations in a duplicate situation, you can use SQL as well (untested):

proc sql;
   create table want as
      select *
      from have
      group by id, visitdate
      having count(*) > 1
   ;
quit,

If you just wish to output the duplicate observation(s), use proc sort with the dupout= option.

Re: Finding redundant entries for same ID and date

ballardw — Thu, 11 Feb 2016 13:48:42 GMT

And yet another possibility

proc freq data=have noprint;

tables Id*visitdate/list out=dups (where=(count>1));

run;

Re: Finding redundant entries for same ID and date

mcdj — Thu, 11 Feb 2016 19:50:19 GMT

thanks, worked perfectly!