How about this :
data have;
informat STDT ENDT date9.;
format STDT ENDT date9.;
input subjid STDT ENDT;
cards;
100 02Oct2013 08Oct2013
100 09Oct2013 15Oct2013
100 16Oct2013 22Oct2013
100 23Oct2013 29Oct2013
100 30Oct2013 05Nov2013
100 06Nov2013 12Nov2013
100 13Nov2013 19Nov2013
100 20Nov2013 26Nov2013
100 27Nov2013 03Dec2013
100 04Dec2013 10Dec2013
100 11Dec2013 17Dec2013
100 18Dec2013 24Dec2013
100 25Dec2013 31Dec2013
100 01Jan2014 07Jan2014
100 08Jan2014 14Jan2014
100 15Jan2014 21Jan2014
100 22Jan2014 28Jan2014
100 29Jan2014 04Feb2014
100 05Feb2014 11Feb2014
100 12Feb2014 18Feb2014
100 19Feb2014 25Feb2014
100 26Feb2014 04Mar2014
100 05Mar2014 11Mar2014
100 12Mar2014 18Mar2014
100 19Mar2014 25Mar2014
100 26Mar2014 01Apr2014
100 02Apr2014 08Apr2014
101 24Sep2013 30Sep2013
101 01Oct2013 07Oct2013
101 08Oct2013 14Oct2013
101 15Oct2013 21Oct2013
101 22Oct2013 28Oct2013
101 29Oct2013 04Nov2013
101 05Nov2013 11Nov2013
101 12Nov2013 18Nov2013
101 19Nov2013 25Nov2013
101 26Nov2013 02Dec2013
101 03Dec2013 09Dec2013
101 10Dec2013 16Dec2013
101 17Dec2013 23Dec2013
101 24Dec2013 30Dec2013
101 31Dec2013 06Jan2014
101 07Jan2014 13Jan2014
101 14Jan2014 20Jan2014
101 21Jan2014 27Jan2014
101 28Jan2014 03Feb2014
101 04Feb2014 10Feb2014
101 11Feb2014 17Feb2014
101 18Feb2014 24Feb2014
101 25Feb2014 03Mar2014
101 04Mar2014 10Mar2014
101 11Mar2014 17Mar2014
101 18Mar2014 24Mar2014
101 25Mar2014 31Mar2014
;
run;
data temp;
informat dstdt dendt date9.;
format dstdt dendt date9.;
input subjid dstdt dendt;
cards;
100 25OCT2013 17MAR2014
100 18MAR2014 28APR2014
101 11OCT2013 .
;
run;
data want;
if _n_ eq 1 then do;
if 0 then set temp;
declare hash h(dataset:'temp',multidata:'y');
h.definekey('subjid') ;
h.definedata('dstdt','dendt');
h.definedone();
end;
set have;
flag='N';
rc=h.find();
do while(rc=0);
if STDT <= dstdt< ENDT then do;
if dstdt <= dendt<ENDT or missing(dendt) then flag='Y';
if dendt >= ENDT then flag='Y';
end;
if dstdt < STDT then do;
if STDT <= dendt < ENDT then flag='Y';
if dendt > ENDT then flag='Y';
end;
rc=h.find_next();
end;
drop rc dstdt dendt;
run;
Xia Keshan
Message was edited by: xia keshan
Hi Keshan,
I Am not really familiar with hash.is it possible to have this code in data step or sql
thanks
sam
Sure.
data have;
informat STDT ENDT date9.;
format STDT ENDT date9.;
input subjid STDT ENDT;
cards;
100 02Oct2013 08Oct2013
100 09Oct2013 15Oct2013
100 16Oct2013 22Oct2013
100 23Oct2013 29Oct2013
100 30Oct2013 05Nov2013
100 06Nov2013 12Nov2013
100 13Nov2013 19Nov2013
100 20Nov2013 26Nov2013
100 27Nov2013 03Dec2013
100 04Dec2013 10Dec2013
100 11Dec2013 17Dec2013
100 18Dec2013 24Dec2013
100 25Dec2013 31Dec2013
100 01Jan2014 07Jan2014
100 08Jan2014 14Jan2014
100 15Jan2014 21Jan2014
100 22Jan2014 28Jan2014
100 29Jan2014 04Feb2014
100 05Feb2014 11Feb2014
100 12Feb2014 18Feb2014
100 19Feb2014 25Feb2014
100 26Feb2014 04Mar2014
100 05Mar2014 11Mar2014
100 12Mar2014 18Mar2014
100 19Mar2014 25Mar2014
100 26Mar2014 01Apr2014
100 02Apr2014 08Apr2014
101 24Sep2013 30Sep2013
101 01Oct2013 07Oct2013
101 08Oct2013 14Oct2013
101 15Oct2013 21Oct2013
101 22Oct2013 28Oct2013
101 29Oct2013 04Nov2013
101 05Nov2013 11Nov2013
101 12Nov2013 18Nov2013
101 19Nov2013 25Nov2013
101 26Nov2013 02Dec2013
101 03Dec2013 09Dec2013
101 10Dec2013 16Dec2013
101 17Dec2013 23Dec2013
101 24Dec2013 30Dec2013
101 31Dec2013 06Jan2014
101 07Jan2014 13Jan2014
101 14Jan2014 20Jan2014
101 21Jan2014 27Jan2014
101 28Jan2014 03Feb2014
101 04Feb2014 10Feb2014
101 11Feb2014 17Feb2014
101 18Feb2014 24Feb2014
101 25Feb2014 03Mar2014
101 04Mar2014 10Mar2014
101 11Mar2014 17Mar2014
101 18Mar2014 24Mar2014
101 25Mar2014 31Mar2014
;
run;
data temp;
informat dstdt dendt date9.;
format dstdt dendt date9.;
input subjid dstdt dendt;
cards;
100 25OCT2013 17MAR2014
100 18MAR2014 28APR2014
101 11OCT2013 .
;
run;
proc sql;
create table x(drop=f) as
select a.*,case
when STDT <= dstdt< ENDT then case
when dstdt <= dendt<ENDT or missing(dendt) then 'Y'
when dendt >= ENDT then 'Y'
end
when dstdt < STDT then case
when STDT <= dendt < ENDT then 'Y'
when dendt > ENDT then 'Y'
end
else 'N'
end as f ,case when sum(calculated f='Y') gt 0 then 'Y' else 'N' end as flag
from have as a,temp as b
where a.subjid=b.subjid
group by a.subjid,a.STDT,a.ENDT;
create table want as
select distinct * from x order by subjid,STDT;
quit;
Xia Keshan
Message was edited by: xia keshan
Simplify your rules, it could be as:
proc sql;
select a.*,case
when stdt<=dstdt<endt or dstdt<stdt<=dendt then 'y'
else 'n'
end as flag
from have a, (select subjid,min(dstdt) as dstdt,max(dendt) as dendt from temp group by subjid) b
where a.subjid=b.subjid;
quit;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.