topic Re: need help to remove duplicates in SAS Programming

need help to remove duplicates

vraj1 — Wed, 18 Jan 2017 11:26:08 GMT

subject	id	text	start date	time	STOP DATE
39	Only in 1A	nature	09 Oct 2014	07:15
39	Only in 1B	beauty	UN Mar 2015		10 Sep 2015
45	Only in 1A	HUMAN	09 Oct 2014

I need to remove subjects which has stop date in "Only in 1B" if the same subject has "Only in 1A" observation also.

Re: need help to remove duplicates

Shmuel — Wed, 18 Jan 2017 11:44:37 GMT

Assumed data is sorted by subject id:

data want;

set have;

by suject id;

retain flag_remove;

if first.id then flag_remove=0;

if id = '1A' than flag_remove = 1;

if id = '1B' and flag_remove=1 then delete;

run;

Re: need help to remove duplicates

vraj1 — Wed, 18 Jan 2017 11:55:53 GMT

should it be if first.subject then flag_remove=0; ??

Re: need help to remove duplicates

vraj1 — Wed, 18 Jan 2017 11:59:42 GMT

But in this case i need to check if stop date is present for only 1B and then delete.

Re: need help to remove duplicates

Astounding — Wed, 18 Jan 2017 12:09:03 GMT

Do you need to remove ALL the observations for that subject, or just the 1B observations?

Re: need help to remove duplicates

vraj1 — Wed, 18 Jan 2017 12:11:27 GMT

If for the same subject i have both 1A and 1B and i have stop date for 1B and missing stop date for 1A then only i need to remove 1B obs. else keep as it is.

Re: need help to remove duplicates

Kurt_Bremser — Wed, 18 Jan 2017 12:21:18 GMT

data delfile (keep=subject);
set have;
by subject;
retain delflag1 delflag2;
if first.subject
then do;
  delflag1 = 0;
  delflag2 = 0;
end;
if id = 'Only in 1A' then delflag1 = 1;
if id = 'Only in 1B' and stop_date ne . then delflag2 = 1;
if last.subject and delflag1 and delflag2 then output;
run;

data want;
merge
  have (in=a)
  delfile (in=b)
;
by subject;
if a and not b;
run;

Re: need help to remove duplicates

Kurt_Bremser — Wed, 18 Jan 2017 12:14:53 GMT

@vraj1 wrote:

If for the same subject i have both 1A and 1B and i have stop date for 1B and missing stop date for 1A then only i need to remove 1B obs. else keep as it is.

So you just moved the goalposts.

Re: need help to remove duplicates

vraj1 — Wed, 18 Jan 2017 12:20:06 GMT

i get NOTE: Variable last.id is uninitialized.

Re: need help to remove duplicates

Kurt_Bremser — Wed, 18 Jan 2017 12:22:05 GMT

@vraj1 wrote:

i get NOTE: Variable last.id is uninitialized.

Corrected my code from last.id to last.subject.

Happens when one has to work without test data.

Re: need help to remove duplicates

vraj1 — Wed, 18 Jan 2017 12:25:25 GMT

Thanks a lot Kurt

Re: need help to remove duplicates

vraj1 — Wed, 18 Jan 2017 12:28:23 GMT

Sorry for asking but in this case i am loosing those subjects whereas i need the subject with only 1A observation and remove 1B obs in the case of stop date present in 1B and not in 1A. Sorry for all the confusion

Re: need help to remove duplicates

Kurt_Bremser — Wed, 18 Jan 2017 12:32:51 GMT

So, for a given subject, you want to remove the "1B" observation if

- the "1B" observation has a stop_date

- a previous "1A" observation had no stop_date

OTOH, the observation would stay when either

- no previous "1A" observation is present

- a previous "1A" observation does not have a stop_date

- the "1B" observation has no stop_date

Re: need help to remove duplicates

vraj1 — Wed, 18 Jan 2017 12:36:45 GMT

remove the "1B" observation if

- the "1B" observation has a stop_date

- a previous "1A" observation had no stop_date

If there is only one onservation for a subject with either 1A or 1B it stays as it is.

if there is stop date for 1A and stop date for 1B then both stays.

If there is no stop date for 1B then also it stays

Re: need help to remove duplicates

Kurt_Bremser — Wed, 18 Jan 2017 12:43:12 GMT

data want;
set have;
by subject;
retain delflag;
if first.subject then delflag = 0;
if id = 'Only in 1A' and stop_date = . then delflag = 1;
if id = 'Only in 1B' and stop_date ne . and delflag then delete;
run;

Re: need help to remove duplicates

vraj1 — Wed, 18 Jan 2017 12:51:55 GMT

Now it works. Thanks a lot Kurt 🙂