topic Re: Removing Duplicate Dates with conditions in SAS Programming

Removing Duplicate Dates with conditions

sjm — Sat, 09 May 2020 13:11:27 GMT

Hi All,

The datafile below records annual data on four firm characteristics (A,B,C and D) that has duplicate values in some years (namely 2014 for firm 1 and 2016 for firm 2). There should however be only one annual observation per firm. I need to remove the duplicate observation that occurs in those years. The criteria is to accept the most recent date if there are at least two variables (A,B, C or D) with non-missing values for that observation. Otherwise, accept the duplicate firm-year observation instead. The example datafile is given below:



Obs	Firm	Date	A	B	C	D
1	1	30/6/2014	1.6		3.6
2	1	31/12/2014	1.2	3.1	3.0	5.6
3	1	31/12/2015	1.7	2.5	2.3	5.1
4	1	31/12/2016	1.4	2.3	1.9	6.0
5	2	31/12/2014	6.2	5.7	7.2	8.0
6	2	31/12/2015	5.8	6.2	7.0	8.2
7	2	31/3/2016	5.9	6.4	6.5	8.9
8	2	30/9/2016	6.5	7.0

Based on the critera, obs 2 should be chosen for firm 1 since it is the most recent date and there are more than two variables without missing values. For firm 2, obs 7 should be chosen since obs 8 with the most recent date, has values missing for more than 2 variables, while values for all four variables are available for obs 7. The resulting output should look like:

Firm	Date	A	B	C	D
1	31/12/2014	1.2	3.1	3.0	5.6
1	31/12/2015	1.7	2.5	2.3	5.1
1	31/12/2016	1.4	2.3	1.9	6.0
2	31/12/2014	6.2	5.7	7.2	8.0
2	31/12/2015	5.8	6.2	7.0	8.2
2	31/3/2016	5.9	6.4	6.5	8.9

I tried to adapt a code that I found (see below

data test1;
	set test;
	year=year(date);
run;
data test2;
	set test1;
	by firm year;
	retain pre_date; drop prev_date;
	if first.firm then do;
		prev_year=year;
		output;
		end; else;
	if year-prev_year>1 then do;
	output;
	end; else delete;
run;

but could not get the result I wanted. Would appreciate your help please. Thanks.

Re: Removing Duplicate Dates with conditions

ed_sas_member — Sat, 09 May 2020 13:22:41 GMT

Hi @sjm

Just a clarification: for firm 2, you says that "obs 7 should be chosen since obs 8 with the most recent date, has values missing for more than 2 variables" -> but obs 7 has only 2 missing values.

Is the rule to consider the most recent date if 0 or 1 value max are missing?

Best,

Re: Removing Duplicate Dates with conditions

sjm — Sat, 09 May 2020 13:46:41 GMT

Thanks for your question. Sorry about that! It should be missing more than one. But the main idea of the rule is to try to use the most recent observation as much as possible unless that observation has too many variables with missing values, in which case the duplicate is chosen.

Re: Removing Duplicate Dates with conditions

novinosrin — Sat, 09 May 2020 18:24:59 GMT

Hi @sjm Assuming I understand your requirement, here is a solution


data have;
input Obs	Firm	Date :ddmmyy10.	A	B	C	D;
format date ddmmyy10.;
cards;
1	1	30/6/2014	1.6	.	3.6	.
2	1	31/12/2014	1.2	3.1	3	5.6
3	1	31/12/2015	1.7	2.5	2.3	5.1
4	1	31/12/2016	1.4	2.3	1.9	6
5	2	31/12/2014	6.2	5.7	7.2	8
6	2	31/12/2015	5.8	6.2	7	8.2
7	2	31/3/2016	5.9	6.4	6.5	8.9
8	2	30/9/2016	6.5	7	.	.
;


data want;
 do _n_=1 by 1 until(last.date);
  set have;
  by firm date groupformat;
  format date year.;
  array t A	B	C	D;
  if n(of t(*))>=_n then do;
    _iorc_=date;
	_n= n(of t(*));
  end;
 end;
 do _n_=1 to _n_;
  set have;
  if date=_iorc_ then output;
 end;
 _iorc_=.;
 drop _:;
run;
proc datasets lib=work noprint;
 modify want;
 format date ddmmyy10.;
run;

proc print noobs;run;

Re: Removing Duplicate Dates with conditions

ed_sas_member — Sat, 09 May 2020 15:08:20 GMT

Hi @sjm

Another question comes up to my mind: what if you have several observations for 1 company and a specific year, and all fo them have 2 or more missing values? Should we take the latest date?

I have added some use cases in the datalines accordingly to clarify my purpose.

Here is an SQL code that will give the result. For the mentioned use case, it takes the max value (cf. comment)

Best,

data have;
	infile datalines dlm="," dsd missover;
	input Obs Firm Date:DDMMYY10. A B C D;
	format date DDMMYY10.;
	datalines;
1,1,30/6/2014,1.6,.,3.6,.
2,1,31/12/2014,1.2,3.1,3.0,5.6
3,1,31/12/2015,.,4,2.3,5.1
4,1,31/12/2016,1.4,2.3,1.9,6.0
5,2,31/12/2014,6.2,5.7,7.2,8.0
6,2,30/12/2015,.,.,7.0,.
7,2,31/12/2015,.,.,7.0,8.2
8,2,31/3/2016,5.9,6.4,6.5,8.9
9,2,28/4/2016,5.9,6.4,6.5,8.9
10,2,30/9/2016,6.5,7.0,7,.
;
run;

proc sql;
	
	create table want as

	/****************************/
	/*obs with 1 record per year*/
	select *
	from have
	group by Firm, year(date)
	having count(year(date))=1
	/****************************/

	union all corr

	/*********************************************************************/
	/*obs with >1 record per year and all records with > 1 missing values*/	
	select a.*
	from have as a right join
		(select Firm, max(date) as Date2 format=DDMMYY10. /* Min Max ??*/
		from have
		group by Firm, year(date)
		having count(year(date))>1 and min(nmiss(A,B,C,D)) > 1) as b
		on a.Firm=b.Firm and a.Date=b.Date2
	/*********************************************************************/

	union all corr

	/*********************************************************************/
	/*obs with >1 record per year and at least one record with 0 or 1 missing value*/
	select a.*
	from have as a right join
		(select Firm, max(date) as Date2 format=DDMMYY10.
		from (select *
			  from have
			  group by Firm, year(date)
	     	  having count(year(date))>1 and min(nmiss(A,B,C,D)) <= 1)
	     where nmiss(A,B,C,D)<= 1
	     group by Firm, year(date)) as b
	     on a.Firm=b.Firm and a.Date=b.Date2
	/*********************************************************************/
	
	 order by obs;
	 
quit;

Re: Removing Duplicate Dates with conditions

Shmuel — Sat, 09 May 2020 15:27:19 GMT

Check next code:

data have;
infile cards truncover dlm='09'x;
input Obs Firm Date :ddmmyy10. A B C D;
yy = year(date);
format date ddmmyy10.;
cards;
1	1	30/6/2014	1.6	.	3.6	.
2	1	31/12/2014	1.2	3.1	3	5.6
3	1	31/12/2015	1.7	2.5	2.3	5.1
4	1	31/12/2016	1.4	2.3	1.9	6
5	2	31/12/2014	6.2	5.7	7.2	8
6	2	31/12/2015	5.8	6.2	7	8.2
7	2	31/3/2016	5.9	6.4	6.5	8.9
8	2	30/9/2016	6.5	7	.	.
;
run;

data temp;
 set have;
     miss = nmiss(a,b,c,d);
run;
proc sort data=temp; by descending yy miss; run;

data want;
 set temp;
  by descending yy;
     if first.yy or miss=0;
     drop yy;
run;
proc sort data=want; by date; run;

Re: Removing Duplicate Dates with conditions

sjm — Sun, 10 May 2020 04:32:36 GMT

Thank you for your suggestion. Could I check whether the second part of the code that selects "obs with 1 record per year and at least one record with 0 or 1 missing value" also captures the situation where there are three same-year observations for the firm that all have no missing values. The rule in such a case would be to pick the observation with max(date).

Re: Removing Duplicate Dates with conditions

Shmuel — Sun, 10 May 2020 04:57:12 GMT

Its not clear to me what exactly you want to check.

if it is to select the last date just change:

1) sort by date instead by yy

2) select by if first.date instead first.yy

or maybe you are looking for:

if first.yy or miss > 1;

Re: Removing Duplicate Dates with conditions

sjm — Sun, 10 May 2020 05:45:15 GMT

Thank you for your suggestion, Shmuel. Regarding your question: what I wanted was to select one annual observation from two or more annual observations on the same firm. The criteria was based on, most importantly, the observation with the most recent date. However, if the observation did not have much information i.e. in terms of having variables with missing values, then I would use one of the other two dates within the same year which had more information i.e. more variables with non-missing values.

Re: Removing Duplicate Dates with conditions

Shmuel — Sun, 10 May 2020 05:58:54 GMT

The posted code fits the results you asked.

In case there is some year not fitting what you want,

please point to that line or add new lines to the test example.

Re: Removing Duplicate Dates with conditions

ed_sas_member — Sun, 10 May 2020 08:00:08 GMT

Hi @sjm

Thank you for the quick reply.

I have added such a case in the below data (Firm2, 2017-> records 11, 12, 13):

data have;
	infile datalines dlm="," dsd missover;
	input Obs Firm Date:DDMMYY10. A B C D;
	format date DDMMYY10.;
	datalines;
1,1,30/6/2014,1.6,.,3.6,.
2,1,31/12/2014,1.2,3.1,3.0,5.6
3,1,31/12/2015,.,4,2.3,5.1
4,1,31/12/2016,1.4,2.3,1.9,6.0
5,2,31/12/2014,6.2,5.7,7.2,8.0
6,2,30/12/2015,.,.,7.0,.
7,2,31/12/2015,.,.,7.0,8.2
8,2,31/3/2016,5.9,6.4,6.5,8.9
9,2,28/4/2016,5.9,6.4,6.5,8.9
10,2,30/9/2016,6.5,7.0,7,.
11,2,28/1/2017,5,6.4,6.5,8.9
12,2,28/2/2017,9,6.4,6.5,8.9
13,2,28/4/2017,8,6.4,6.5,8.9
;
run;

The programs correctly picks up record=13:

Best,

Re: Removing Duplicate Dates with conditions

Shmuel — Sun, 10 May 2020 10:17:45 GMT

I suppose you want to select lines per year per farm, so I modified the program:

data have;
	infile datalines dlm="," dsd missover;
	input Obs Firm Date:DDMMYY10. A B C D;
	format date DDMMYY10.;
	yy = year(date);
	datalines;
1,1,30/6/2014,1.6,.,3.6,.
2,1,31/12/2014,1.2,3.1,3.0,5.6
3,1,31/12/2015,.,4,2.3,5.1
4,1,31/12/2016,1.4,2.3,1.9,6.0
5,2,31/12/2014,6.2,5.7,7.2,8.0
6,2,30/12/2015,.,.,7.0,.
7,2,31/12/2015,.,.,7.0,8.2
8,2,31/3/2016,5.9,6.4,6.5,8.9
9,2,28/4/2016,5.9,6.4,6.5,8.9
10,2,30/9/2016,6.5,7.0,7,.
11,2,28/1/2017,5,6.4,6.5,8.9
12,2,28/2/2017,9,6.4,6.5,8.9
13,2,28/4/2017,8,6.4,6.5,8.9
;
run;


data temp;
 set have;
     miss = nmiss(a,b,c,d);
run;
proc sort data=temp; by firm descending yy miss; run;

data want;
 set temp;
  by firm descending yy;
     if first.yy or miss=0;
     drop yy;
run;
proc sort data=want; by firm date; run;

I don't understand why you selected for firm=2 year=2016 - line 10 and not lines 8 and 9.

line 10 has one missing value while lines 8 and 9 are full.

Re: Removing Duplicate Dates with conditions

Shmuel — Sun, 10 May 2020 10:21:19 GMT

In case of any issue please post the result lined and the expected lines marking the difference and explain why you selected the specific line(s) instead.

Re: Removing Duplicate Dates with conditions

sjm — Sun, 10 May 2020 13:22:27 GMT

Line 10 was selected because it has the most recent date and it meets the criteria that there is at least more than 1 variable with a nonmissing value. More recent dates are preferred.

Re: Removing Duplicate Dates with conditions

Tom — Sun, 10 May 2020 13:46:20 GMT

One way is to first figure out the minimum number of missing values for that firm for that year. Then you can pick the most recent date with that number of missing values.

Let's start by turning your listing into data.

data have;
  input firm date :ddmmyy. a b c d;
  format date yymmdd10.;
cards;
1 30/06/2014 1.6   . 3.6 .
1 31/12/2014 1.2 3.1   3 5.6
1 31/12/2015 1.7 2.5 2.3 5.1
1 31/12/2016 1.4 2.3 1.9 6
2 31/12/2014 6.2 5.7 7.2 8
2 31/12/2015 5.8 6.2   7 8.2
2 31/03/2016 5.9 6.4 6.5 8.9
2 30/09/2016 6.5   7   . .
;

Now we can use an SQL step to make the new variables and calculate the min missing and also do the sorting.

proc sql;
create table step1 as 
  select *
       , year(date) as yr
       , nmiss(a,b,c,d) as nmiss
       , min(calculated nmiss) as min_miss
  from have
  group by firm,yr
  order by firm,yr,date desc 
;
quit;

Now we just need to find the first place where nmiss matches min_miss and output that record only.

data want;
  set step1;
  by firm yr;
  if first.yr then found=0;
  if not found and min_miss=nmiss then do;
    found=1;
    output;
  end;
  retain found;
run;

Re: Removing Duplicate Dates with conditions

Shmuel — Sun, 10 May 2020 14:11:19 GMT

@Tom, I like your approach but your code
selects obs 9 (apr 2016) instead obs 10 (sep 2016)

Re: Removing Duplicate Dates with conditions

Shmuel — Sun, 10 May 2020 14:24:18 GMT

Next code results fit your expected:

data have;
	infile datalines dlm="," dsd missover;
	input Obs Firm Date:DDMMYY10. A B C D;
	format date DDMMYY10.;
	yy = year(date);
	datalines;
1,1,30/6/2014,1.6,.,3.6,.
2,1,31/12/2014,1.2,3.1,3.0,5.6
3,1,31/12/2015,.,4,2.3,5.1
4,1,31/12/2016,1.4,2.3,1.9,6.0
5,2,31/12/2014,6.2,5.7,7.2,8.0
6,2,30/12/2015,.,.,7.0,.
7,2,31/12/2015,.,.,7.0,8.2
8,2,31/3/2016,5.9,6.4,6.5,8.9
9,2,28/4/2016,5.9,6.4,6.5,8.9
10,2,30/9/2016,6.5,7.0,7,.
11,2,28/1/2017,5,6.4,6.5,8.9
12,2,28/2/2017,9,6.4,6.5,8.9
13,2,28/4/2017,8,6.4,6.5,8.9
;
run;


data temp1;
 set have;
     miss = nmiss(a,b,c,d);
run;
proc sort data=temp1; by firm descending yy miss; run;

data temp2;
 set temp1;
  by firm descending yy;
     if first.yy and last.yy or miss < 3 
        then flag_sel = 1;
        else flag_sel=0;
run;
proc sort data=temp2; by firm date flag_sel; run;

data want;
 set temp2 (where=(flag_sel=1));
  by firm yy;
     if last.yy;
     drop flag_sel yy;
run;

Re: Removing Duplicate Dates with conditions

Tom — Sun, 10 May 2020 16:34:11 GMT

If you want to allow selecting an observations with more than the actual minimum number of missing values just change the logic of finding the first date.

  if not found and max(1,min_miss)>=nmiss then do;

Re: Removing Duplicate Dates with conditions

sjm — Mon, 11 May 2020 11:09:50 GMT

Thank you for your solution. It works very well!

I tried using the same code (modified slightly) to eliminate duplicates for a quarterly data set but ran into some difficulties and would be grateful for your help.

Unlike the other dataset that had yearly observations with different dates, the duplicates in this quarterly dataset have identical dates, so I was not able to use MAX(.) efficiently in the code.

For the quarterly dataset, the rule to select the correct date, is that the observation must have more variables with non-missing values. For duplicates with the same number of non-missing variables, the rule is to select the observation where DATAFQTR has the same calendar year as DATADATE. DATAFQTR is however a character variable.

I have attached the code and a sample of the SAS datafile that highlights these problem for two firms. They are identified by their respective GVKEY’s.

Notice that firm 206883 has duplicate observations (17 and 18) that differ only for DATAFQTR. Here, observation 17 should be chosen since its DATAFQTR has the same calendar year as DATADATE (i.e. 2009). For observations 21 and 22 for the same firm, observation 21 should be chosen since it has more variables with non-missing values. Here, DATAFTQR has the same calendar date as DATADATE.

Firm 212151 has duplicate observations 49 and 50. In this case, observation 49 should be chosen since it has more variables with non-missing values even if DATAFQTR does not have the same calendar date as DATAFQTR.

The variable DATAFQTR could possibly help to solve this problem but I am not sure how to use it here. Thanks.

proc sql;
	
	create table want as

	/****************************/
	/*obs with 1 record per year*/
	select *
	from have
	group by Firm, year(date)
	having count(year(date))=1
	/****************************/

	union all corr

	/*********************************************************************/
	/*obs with >1 record per year and all records with > 1 missing values*/	
	select a.*
	from have as a right join
		(select Firm, max(date) as Date2 format=DDMMYY10. /* Min Max ??*/
		from have
		group by Firm, year(date)
		having count(year(date))>1 and min(nmiss(A,B,C,D)) > 1) as b
		on a.Firm=b.Firm and a.Date=b.Date2
	/*********************************************************************/

	union all corr

	/*********************************************************************/
	/*obs with >1 record per year and at least one record with 0 or 1 missing value*/
	select a.*
	from have as a right join
		(select Firm, max(date) as Date2 format=DDMMYY10.
		from (select *
			  from have
			  group by Firm, year(date)
	     	  having count(year(date))>1 and min(nmiss(A,B,C,D)) <= 1)
	     where nmiss(A,B,C,D)<= 1
	     group by Firm, year(date)) as b
	     on a.Firm=b.Firm and a.Date=b.Date2
	/*********************************************************************/
	
	 order by obs;
	 
quit;

Re: Removing Duplicate Dates with conditions

sjm — Mon, 11 May 2020 11:29:35 GMT

Sorry, the wrong SAS code was attached. Please use this one instead. Thanks.

proc sql;	
	create table temp5 as
	/****************************/
	/*obs with 1 record per year*/
	select *
	from testfile
	group by gvkey, year(datadate), qtr(datadate)
	having count(qtr(datadate))=1
	/****************************/
	union all corr
	/*********************************************************************/
	/*obs with >1 record per year and all records with > 1 missing values*/	
	select a.*
	from testfile as a right join
		(select gvkey, max(datadate) as Date2 format=DDMMYY10. /* Min Max ??*/
		from testfile
		group by gvkey, year(datadate), qtr(datadate)
		having count(qtr(datadate))>1 and min(nmiss(atq,cheq,ltq,revtq)) > 1) as b
		on a.gvkey=b.gvkey and a.datadate=b.Date2
	/*********************************************************************/
	union all corr
	/*********************************************************************/
	/*obs with >1 record per year and at least one record with 0 or 1 missing value*/
	select a.*
	from testfile as a right join
		(select gvkey, max(datadate) as Date2 format=DDMMYY10.
		from (select *
			  from testfile
			  group by gvkey, year(datadate), qtr(datadate)
	     	  having count(qtr(datadate))>1 and min(nmiss(atq,cheq,ltq,revtq)) <= 1)
	     where nmiss(atq,cheq,ltq,revtq)<= 1
	     group by gvkey, year(datadate), qtr(datadate)) as b
	     on a.gvkey=b.gvkey and a.datadate=b.Date2;
quit;