Check the dates in loop

[Ksharp]

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

[sam369]

Hi Keshan,

I Am not really familiar with hash.is it possible to have this code in data step or sql

thanks

sam

[Ksharp]

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

[slchen]

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;