topic Re: Deleting first few duplicate observations within each subject in SAS Procedures

Deleting first few duplicate observations within each subject

heavensong — Wed, 05 Aug 2015 04:27:36 GMT

Dear all,

I have a longitudinal dataset in which each patient is recorded with multiple dates. Each date is counted as a separate row. Note that a patient could have multiple entries for the same date. The data layout is as follow:

ID Date Var1 Var2

1 2002 4 5

1 2002 5 7

1 2003 7 9

2 2001 4 8

2 2002 3 8

Ideally, I would like to delete observations from the first recored date for each patient, so the output data would look like:

ID Date Var1 Var2

1 2003 7 9

2 2002 3 8

I have tried proc sort with nodupkey as well as proc sql distinct but none of them gave me the output i want. I was also trying to come up with an indicator to code for the first date within each patient but did not succeed. I'd really appreciate if anyone could help!

Re: Deleting first few duplicate observations within each subject

PhilC — Wed, 05 Aug 2015 12:07:12 GMT

See PROC SORT examples especially with the option NODUPKEY.

Re: Deleting first few duplicate observations within each subject

MadhuKorni — Wed, 05 Aug 2015 12:14:14 GMT

proc sort data = have;

by id date;

run;

data want;

set have;

if last.id;

by id;

run;

Re: Deleting first few duplicate observations within each subject

Ksharp — Wed, 05 Aug 2015 12:17:38 GMT

Code: Program

data have;
input ID   Date   Var1   Var2;
cards;
1 2002 4 5
1 2002 5 7
1 2003 7 9
2 2001 4 8
2 2002 3 8
;
run;

data want;
 set have;
 by id date notsorted;
 if first.id then n=0;
 n+first.date;
 if n ne 1;
 drop n;
run;

Re: Deleting first few duplicate observations within each subject

heavensong — Mon, 10 Aug 2015 02:43:06 GMT

Thank you so much. This is exactly what I was looking for.

Re: Deleting first few duplicate observations within each subject

MikeZdeb — Mon, 10 Aug 2015 22:09:45 GMT

Hi. You tried SQL. Did you try this ...

proc sql;

create table want as select * from have group by id having date ^= min(date);

quit;

Re: Deleting first few duplicate observations within each subject

MikeZdeb — Mon, 10 Aug 2015 22:59:56 GMT

Hi. I think that the only problem with this is if there are multiple occurrences of dates after the first date, for example ...

data have;

input ID Date Var1 Var2 @@;

cards;

1 2002 4 5 1 2002 5 7 1 2003 7 9 1 2003 9 9

2 2001 4 8 2 2002 3 8 2 2002 9 9

;

data want;

set have;

by id;

if last.id;

run;

WANT would be ...

Obs ID Date Var1 Var2

1 1 2003 9 9

2 2 2002 9 9

and miss one "not first date" for each ID. How about ...

data want (drop=first_date);

set have;

by id;

first_date + first.id*date;

if date ne first_date then output;

if last.id then call missing(first_date);

run;

WANT would be ...

Obs ID Date Var1 Var2

1 1 2003 7 9

2 1 2003 9 9

3 2 2002 3 8

4 2 2002 9 9

(same answer you'd get with Xia's data step answer or my PROC SQL answer)

Re: Deleting first few duplicate observations within each subject

Haikuo — Tue, 11 Aug 2015 02:24:26 GMT

Mike, I suppose you want GT (>) instead of ^=, just in case some dates are missing.

Re: Deleting first few duplicate observations within each subject

MikeZdeb — Tue, 11 Aug 2015 03:05:40 GMT

Sure, thanks.