09-22-2017 04:11 PM - last edited on 09-22-2017 04:17 PM by Reeza
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.
09-22-2017 05:17 PM
09-22-2017 05:32 PM
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;
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).
09-22-2017 07:42 PM
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;