topic Re: Extract concurrence dates in SAS Programming

Extract concurrence dates

s_manoj — Fri, 10 Aug 2018 09:24:14 GMT

Hi all,

I have been working on a data to extract concurrence dates (dates that overlap on other), I have cracked a code and go desired output, but I have been trying for even simpler code that reduces process time, so I taught of posting here so that I can gain some knowledge.

Note: overlap dates have to be checked for groups within ID and aes vars.

Heres My data:

data ae;
input id aes$ stdt:date9. aeendt:date9.;
format stdt:date8. aeendt:date8.;
cards;
1 x 20nov2016 30nov2017
1 x 28nov2016 03dec2017
1 x 04dec2017 08dec2018
1 y 04nov2016 20nov2017
2 z 01jan2016 08jan2017
2 z 30dec2015 10jan2017
2 z 01nov2017 03nov2017
3 x 01jan2016 10jan2016
3 x 01feb2016 28feb2016
3 y 01feb2016 28jan2016
4 x 01jan2016 08jan2016
4 x 01jan2016 08jan2016
4 x 02jan2016 08jan2016
;
run;

required output is:

 id aes stdt aeendt 
 1 x 20NOV16 30NOV17 
 1 x 28NOV16 03DEC17 
 2 z 30DEC15 10JAN17 
 2 z 01JAN16 08JAN17 
 4 x 01JAN16 08JAN16 
 4 x 01JAN16 08JAN16 
 4 x 02JAN16 08JAN16

Here's my code to reach above output:

proc sort data = ae;
by id aes stdt;
run;
data xyz1;
set ae ;
by id aes;
x = _n_ - 1;
n = _n_;
if (first.aes = 0  & stdt < lag(aeendt)) | (first.aes=0 & lag(aeendt) eq .) then do;
	set ae point = x;
	z = x;
	output;
	set ae point = n;
	z = n;
	output;
end;
proc sort nodupkey ;
by z;
run;

I have been anticipating for a simpler and efficient code that can reduce process time, looking forward for any responses. Just taught to post here so that I can gain some knowledge.

Regards

Manoj

Re: Extract concurrence dates

Ksharp — Fri, 10 Aug 2018 13:36:33 GMT

data ae;
input id aes$ stdt:date9. aeendt:date9.;
format stdt:date8. aeendt:date8.;
cards;
1 x 20nov2016 30nov2017
1 x 28nov2016 03dec2017
1 x 04dec2017 08dec2018
1 y 04nov2016 20nov2017
2 z 01jan2016 08jan2017
2 z 30dec2015 10jan2017
2 z 01nov2017 03nov2017
3 x 01jan2016 10jan2016
3 x 01feb2016 28feb2016
3 y 01feb2016 28jan2016
4 x 01jan2016 08jan2016
4 x 01jan2016 08jan2016
4 x 02jan2016 08jan2016
;
run;
data ae;
 set ae;
  n+1;
run;
data temp;
 set ae;
 do date=stdt to aeendt;
   output;
 end;
 format date date9.;
 drop stdt aeendt;
run;
proc sort data=temp out=temp1;
by id aes date;
run;
proc sql;
create table temp2 as
 select distinct n
  from temp1
   group by id,aes,date
    having count(*) > 1;

create table want as
 select *
  from ae
   where n in (select n from temp2)
    order by n;
quit;

Re: Extract concurrence dates

s_manoj — Fri, 10 Aug 2018 14:16:02 GMT

Thank you @Ksharp
will try this code.

Re: Extract concurrence dates

s_manoj — Sun, 12 Aug 2018 12:46:56 GMT

Hi @Ksharp
checked your code and its working, Thank you for that.
in data step code you have been expanding no of observations,
this step might take lots of time while working on large datasets like > one million record/ 1gb of data...
I am looking for a code which can save execution time, can you help me with that.

Regards
Manoj

Re: Extract concurrence dates

Ksharp — Mon, 13 Aug 2018 14:03:45 GMT

Sure.Assuming your table has been sorted.

data ae;
input id aes$ stdt:date9. aeendt:date9.;
format stdt:date8. aeendt:date8.;
cards;
1 x 20nov2016 30nov2017
1 x 28nov2016 03dec2017
1 x 04dec2017 08dec2018
1 y 04nov2016 20nov2017
2 z 01jan2016 08jan2017
2 z 30dec2015 10jan2017
2 z 01nov2017 03nov2017
3 x 01jan2016 10jan2016
3 x 01feb2016 28feb2016
3 y 01feb2016 28jan2016
4 x 01jan2016 08jan2016
4 x 01jan2016 08jan2016
4 x 02jan2016 08jan2016
;
run;
data want;
 if _n_=1 then do;
   declare hash h();
   declare hiter hi('h');
   h.definekey('date');
   h.definedata('date','n');
   h.definedone();
   
   declare hash k();
   k.definekey('date');
   k.definedone();
 end;
do until(last.aes);
 set ae;
 by id aes;
 do date=stdt to aeendt;
  if h.find()=0 then do;n=n+1;h.replace();end;
    else do;n=1;h.add();end;
 end;
end;

do while(hi.next()=0);
  if n>1 then k.add();
end;

do until(last.aes);
 set ae;
 by id aes;
 do date=stdt to aeendt;
    if k.check()=0 then do;output;leave;end;
 end;
end;

h.clear();k.clear();
drop n date;
run;

proc print;run;

Re: Extract concurrence dates

s_manoj — Tue, 14 Aug 2018 15:52:13 GMT

hi @Ksharp Thank you very much, you are very helpful.