BookmarkSubscribeRSS Feed
s_manoj
Quartz | Level 8

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

 

5 REPLIES 5
Ksharp
Super User
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;
s_manoj
Quartz | Level 8
Thank you @Ksharp
will try this code.
s_manoj
Quartz | Level 8
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
Ksharp
Super User

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;
s_manoj
Quartz | Level 8

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 862 views
  • 2 likes
  • 2 in conversation