DATA Step, Macro, Functions and more

Extract concurrence dates

Reply
Contributor
Posts: 50

Extract concurrence dates

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

 

Super User
Posts: 10,860

Re: Extract concurrence dates

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;
Contributor
Posts: 50

Re: Extract concurrence dates

Thank you @Ksharp
will try this code.
Contributor
Posts: 50

Re: Extract concurrence dates

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
Super User
Posts: 10,860

Re: Extract concurrence dates

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;
Contributor
Posts: 50

Re: Extract concurrence dates

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

 

Ask a Question
Discussion stats
  • 5 replies
  • 89 views
  • 2 likes
  • 2 in conversation