topic Re: Delete duplicate in a given period in New SAS User

Delete duplicate in a given period

dapenDaniel — Wed, 29 Jan 2020 06:16:21 GMT

Hi,

I have a dataset, which has three variables (FirmID, Year and Var1). I would like to delete the duplicate so that the same Var1 is only shown once within 3 years for each firm. What programs do I need to use? Thanks.

have

FirmID	Year	Var1
1001	1995	AA
1001	1995	AB
1001	1995	AC
1001	1995	AD
1001	1996	AA
1001	1996	CS
1001	1996	SW
1001	1997	AC
1001	1997	FD
1001	1998	OL
1001	1999	AD
1001	1999	SC
1001	1999	QW

want

FirmID	Year	Var1
1001	1995	AA
1001	1995	AB
1001	1995	AC
1001	1995	AD
1001	1996	CS
1001	1996	SW
1001	1997	FD
1001	1998	OL
1001	1999	AD
1001	1999	SC
1001	1999	QW

Re: Delete duplicate in a given period

Ksharp — Wed, 29 Jan 2020 08:01:19 GMT

If you don't have a big table ,try SQL,otherwise try Hash Table.

data x;
infile cards expandtabs;
input FirmID	Year	Var1 $;
cards;
1001	1995	AA
1001	1995	AB
1001	1995	AC
1001	1995	AD
1001	1996	AA
1001	1996	CS
1001	1996	SW
1001	1997	AC
1001	1997	FD
1001	1998	OL
1001	1999	AD
1001	1999	SC
1001	1999	QW
;


proc sql;
select *
 from x as a 
  where not exists(
   select * from x 
    where FirmID=a.FirmID and 
     year between a.year-3 and a.year-1 and
      Var1=a.Var1);
quit;

Re: Delete duplicate in a given period

ed_sas_member — Wed, 29 Jan 2020 08:08:51 GMT

Hi @dapenDaniel

Your question looks very similar to a previous one you posted.

Here is an attempt to do that:

proc sort data=have;
	by FirmID Var1 Year;
run;

data have1;
	set have;
	by FirmID Var1 Year;

	retain Year_ref;
	if first.Var1 then Year_ref = year;

	if first.Var1 or Year >= (Year_ref + 3) then output;
	
	drop Year_ref;
run;

proc sort data=have1 out=want;
	by FirmID Year;
run;