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
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;
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;
hi @Ksharp Thank you very much, you are very helpful.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.