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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.