topic Re: Find overlapping on dates in SAS Programming

Find overlapping on dates

iscgonzalez — Fri, 22 Sep 2017 20:17:58 GMT

Hello,

I have a problem where I have to find contracts from clients that have overlapping durations as it is supposed that a client cannot have two or more active contracts.

I have created the following code, but I think this is not the best way to go, since it takes a lot of time on big tables.

does anyone have an idea on how I can Improve the performance on this?

data test;
	infile datalines;
	input client_Id $ contract $ date_begin date_end;
	informat date_begin date_end ddmmyy10.;
	format date_begin date_end date7.;
	cards;
10 A 01/09/2016 30/09/2016
10 B 10/12/2016 24/12/2016
10 C 28/09/2016 07/10/2016
11 D 01/12/2016 24/12/2018
12 E 15/01/2016 07/03/2016
12 F 08/12/2016 24/12/2016
12 G 06/03/2016 07/11/2016
;
run;

%macro overlap;
	%let dt_ini=&dt_ini;
	%let dt_end=&dt_end;
	%let client = &client;
	%let sqlresults =;

	proc sql noprint;
		select count(*) into :sqlresults 
		from TEST 
		where client_id=&client and  
		(date_begin between &dt_ini and &dt_end
		or date_end between &dt_ini and &dt_end);
	QUIT;

	%let sqlresults = &sqlresults;
%mend;

proc fcmp outlib=work.testing.funcs;
	function get_overlap(dt_ini, dt_end, client $);
	rc=run_macro('overlap', dt_ini, dt_end, client, sqlresults);
	return (sqlresults);
	endsub;
	quit;
	
options cmplib=work.testing;

data test2;
	set test;
	date_overlap=get_overlap(date_begin, date_end, client_id)-1;
run;

Edit via @Reeza to format code for legibility.

Re: Find overlapping on dates

LinusH — Fri, 22 Sep 2017 21:17:52 GMT

Interesting solution, I wouldn't come up with that.
My take would probably be first sort by client is and date begin.
Then a data step with RETAIN on a variable you set ronthe previous date end, and check if it's in the date begin and end interval. No idea If that's more efficient than yours - a log could give a clue.

Re: Find overlapping on dates

Astounding — Fri, 22 Sep 2017 21:32:43 GMT

Looks like your middle SQL is doing the heavy lifting here. Why not expand that to do the whole thing?

proc sql noprint;

create table want as select a.*, b.contract as overlapping_contract

from have a, have b

where ( (a.date_begin <= b.date_begin <= a.date_end) or (a.date_begin <= b.date_end <= a.date_end) )

and a.client=b.client and a.contract ne b.contract;

quit;

This the idea, although you may need to fiddle with it.

It's possible you could switch (or may have to switch) to BETWEEN instead of the two sets of <=.

Also, you may want to change <= to get rid of the equal sign ... depends on what you consider "overlapping" to mean.

You can also select the overlapping dates (as long as you rename them, similarly to what was done for OVERLAPPING_CONTRACT).

Re: Find overlapping on dates

Patrick — Fri, 22 Sep 2017 23:42:53 GMT

@iscgonzalez

Something like below could work and eventually performs better than a SQL as it requires only one sort step for your source data.

data test;
	infile datalines;
	input client_Id $ contract $ date_begin date_end;
	informat date_begin date_end ddmmyy10.;
	format date_begin date_end date7.;
	cards;
10 A 01/09/2016 30/09/2016
10 B 10/12/2016 24/12/2016
10 C 28/09/2016 07/10/2016
11 D 01/12/2016 24/12/2018
12 E 15/01/2016 07/03/2016
12 F 08/12/2016 24/12/2016
12 G 06/03/2016 07/11/2016
;
run;

proc sort data=test;
  by client_id date_begin;
run;

data want;
  set test;
  by client_Id;

  set test(firstobs=2 keep=client_Id contract date_begin date_end rename=(contract=contract2 date_begin=date_begin2 date_end=date_end2));

  if last.client_Id then 
    do;
      call missing(max_date_end);
      return;
    end;

  retain max_date_end;
  format max_date_end date7.;
  max_date_end=max(max_date_end,date_end);
  
  if date_begin2<=max_date_end then output;
run;