topic Re: Check the dates in loop in SAS Procedures

Check the dates in loop

sam369 — Fri, 07 Nov 2014 15:14:35 GMT

Hi All,

I need to check the dates and need to flag them. I have more than 1500 unique subjid .

Below is one example

data have;

informat STDT ENDT date9.;

format STDT ENDT date9.;

subjid=100;

input STDT ENDT;

cards;

02Oct2013 08Oct2013

09Oct2013 15Oct2013

16Oct2013 22Oct2013

23Oct2013 29Oct2013

30Oct2013 05Nov2013

06Nov2013 12Nov2013

13Nov2013 19Nov2013

20Nov2013 26Nov2013

27Nov2013 03Dec2013

04Dec2013 10Dec2013

11Dec2013 17Dec2013

18Dec2013 24Dec2013

25Dec2013 31Dec2013

01Jan2014 07Jan2014

08Jan2014 14Jan2014

15Jan2014 21Jan2014

22Jan2014 28Jan2014

29Jan2014 04Feb2014

05Feb2014 11Feb2014

12Feb2014 18Feb2014

19Feb2014 25Feb2014

26Feb2014 04Mar2014

05Mar2014 11Mar2014

12Mar2014 18Mar2014

19Mar2014 25Mar2014

26Mar2014 01Apr2014

02Apr2014 08Apr2014

;

run;

data temp;

informat dstdt dendt date9.;

format dstdt dendt date9.;

subjid=100;

input dstdt dendt;

cards;

25OCT2013 17MAR2014

18MAR2014 28APR2014

;

run;

want:

STDT ENDT FLG

02Oct2013 08Oct2013 N

09Oct2013 15Oct2013 N

16Oct2013 22Oct2013 N

23Oct2013 29Oct2013 Y

30Oct2013 05Nov2013 Y

06Nov2013 12Nov2013 Y

13Nov2013 19Nov2013 Y

20Nov2013 26Nov2013 Y

27Nov2013 03Dec2013 Y

04Dec2013 10Dec2013 Y

11Dec2013 17Dec2013 Y

18Dec2013 24Dec2013 Y

25Dec2013 31Dec2013 Y

01Jan2014 07Jan2014 Y

08Jan2014 14Jan2014 Y

15Jan2014 21Jan2014 Y

22Jan2014 28Jan2014 Y

29Jan2014 04Feb2014 Y

05Feb2014 11Feb2014 Y

12Feb2014 18Feb2014 Y

19Feb2014 25Feb2014 Y

26Feb2014 04Mar2014 Y

05Mar2014 11Mar2014 Y

12Mar2014 18Mar2014 Y

19Mar2014 25Mar2014 Y

26Mar2014 01Apr2014 Y

02Apr2014 08Apr2014 Y

I have two consideration to make, Below are the rules

1) if STDT <= dstdt < ENDT then we need to consider flg

if dstdt <= dendt < ENDT then flg=Y;

if dendt >= ENDT then flg=Y;

2) if dstdt < STDT then we need to consider flg

if STDT <= dendt < ENDT then flg=Y;

if dendt > ENDT then flg=Y;

if these criteria no met then flg=N

Thanks

Sam

Re: Check the dates in loop

art297 — Fri, 07 Nov 2014 16:09:07 GMT

There are a number of ways to accomplish what you want. Here is one that simply applies the logic you specified:

data _null_;

if 0 then set temp nobs=nobs;

CALL SYMPUT('NUMREC',nobs);

stop;

run;

data want (drop=i);

array temp(&numrec.,2) _temporary_;

i=0;

do until (eof1);

set temp end=eof1;

i=i+1;

temp(i,1)=dstdt;

temp(i,2)=dendt;

end;

do until (eof2);

set have end=eof2;

flg='N';

do i=1 to &numrec.;

if STDT <= temp(i,1) < ENDT then do;

if (temp(i,1) <= temp(i,2) < ENDT) or (temp(i,2) >= ENDT) then flg='Y';

end;

if temp(i,1) < STDT then do;

if (STDT <= temp(i,2) < ENDT) or (temp(i,2) > ENDT) then flg='Y';

end;

if flg eq 'Y' then leave;

end;

output;

end;

run;

Re: Check the dates in loop

sam369 — Fri, 07 Nov 2014 20:14:45 GMT

Hi Arthur,

Thank you so much for the help. It work for sample code , not accommodated on real one. I am keeping another loop to pass individual subjects and appending the dataset. i have more than 1500 unique subjid.

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 5Oct2013 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;

want:

SUBJID STDT ENDT FLG

100 02OCT2013 08OCT2013 N

100 09OCT2013 15OCT2013 N

100 16OCT2013 22OCT2013 N

100 23OCT2013 29OCT2013 Y

100 30OCT2013 05NOV2013 Y

100 06NOV2013 12NOV2013 Y

100 13NOV2013 19NOV2013 Y

100 20NOV2013 26NOV2013 Y

100 27NOV2013 03DEC2013 Y

100 04DEC2013 10DEC2013 Y

100 11DEC2013 17DEC2013 Y

100 18DEC2013 24DEC2013 Y

100 25DEC2013 31DEC2013 Y

100 01JAN2014 07JAN2014 Y

100 08JAN2014 14JAN2014 Y

100 15JAN2014 21JAN2014 Y

100 22JAN2014 28JAN2014 Y

100 29JAN2014 04FEB2014 Y

100 05FEB2014 11FEB2014 Y

100 12FEB2014 18FEB2014 Y

100 19FEB2014 25FEB2014 Y

100 26FEB2014 04MAR2014 Y

100 05MAR2014 11MAR2014 Y

100 12MAR2014 18MAR2014 Y

100 19MAR2014 25MAR2014 Y

100 26MAR2014 01APR2014 Y

100 02APR2014 08APR2014 Y

101 24SEP2013 30SEP2013 N

101 01OCT2013 07OCT2013 N

101 08OCT2013 14OCT2013 Y

101 15OCT2013 21OCT2013 N

101 22OCT2013 28OCT2013 N

101 29OCT2013 04NOV2013 N

101 05NOV2013 11NOV2013 N

101 12NOV2013 18NOV2013 N

101 19NOV2013 25NOV2013 N

101 26NOV2013 02DEC2013 N

101 03DEC2013 09DEC2013 N

101 10DEC2013 16DEC2013 N

101 17DEC2013 23DEC2013 N

101 24DEC2013 30DEC2013 N

101 31DEC2013 06JAN2014 N

101 07JAN2014 13JAN2014 N

101 14JAN2014 20JAN2014 N

101 21JAN2014 27JAN2014 N

101 28JAN2014 03FEB2014 N

101 04FEB2014 10FEB2014 N

101 11FEB2014 17FEB2014 N

101 18FEB2014 24FEB2014 N

101 25FEB2014 03MAR2014 N

101 04MAR2014 10MAR2014 N

101 11MAR2014 17MAR2014 N

101 18MAR2014 24MAR2014 N

101 25MAR2014 31MAR2014 N

Thanks

Sam

Re: Check the dates in loop

art297 — Fri, 07 Nov 2014 22:00:39 GMT

Sam,

If you want to run multiple subjects at the same time, then I'd suggest trying something like the following (assuming both data sets are already sorted by subjid):

proc transpose data=have out=formerge1 (drop=_:) prefix=_stdt;

var STDT;

by subjid;

run;

proc transpose data=have out=formerge2 (drop=_:) prefix=_endt;

var ENDT;

by subjid;

run;

data formerge;

set formerge1;

set formerge2;

run;

data want;

merge temp formerge;

by subjid;

array temp1(*) _stdt:;

array temp2(*) _endt:;

i=0;

do until (eof2);

set have end=eof2;

flg='N';

do i=1 to dim(temp1);

if STDT <= temp1(i) < ENDT then do;

if (temp1(i) <= temp2(i) < ENDT) or (temp2(i) >= ENDT) then flg='Y';

end;

if temp1(i) < STDT then do;

if (STDT <= temp2(i) < ENDT) or (temp2(i) > ENDT) then flg='Y';

end;

if flg eq 'Y' then leave;

end;

output;

end;

run;

Re: Check the dates in loop

sam369 — Fri, 07 Nov 2014 22:27:18 GMT

Hi Arthur,

I tried your approach, but almost every flag is 'Y' , did i miss something

i got unexpected output:

subjid STDT ENDT flg

100 02OCT2013 08OCT2013 Y

100 09OCT2013 15OCT2013 Y

100 16OCT2013 22OCT2013 Y

100 23OCT2013 29OCT2013 Y

100 30OCT2013 05NOV2013 Y

100 06NOV2013 12NOV2013 Y

100 13NOV2013 19NOV2013 Y

100 20NOV2013 26NOV2013 Y

100 27NOV2013 03DEC2013 Y

100 04DEC2013 10DEC2013 Y

100 11DEC2013 17DEC2013 Y

100 18DEC2013 24DEC2013 Y

100 25DEC2013 31DEC2013 Y

100 01JAN2014 07JAN2014 Y

100 08JAN2014 14JAN2014 Y

100 15JAN2014 21JAN2014 Y

100 22JAN2014 28JAN2014 Y

100 29JAN2014 04FEB2014 Y

100 05FEB2014 11FEB2014 Y

100 12FEB2014 18FEB2014 Y

100 19FEB2014 25FEB2014 Y

100 26FEB2014 04MAR2014 Y

100 05MAR2014 11MAR2014 Y

100 12MAR2014 18MAR2014 Y

100 19MAR2014 25MAR2014 Y

100 26MAR2014 01APR2014 Y

100 02APR2014 08APR2014 Y

101 24SEP2013 30SEP2013 N

101 01OCT2013 07OCT2013 Y

101 08OCT2013 14OCT2013 Y

101 05OCT2013 21OCT2013 Y

101 22OCT2013 28OCT2013 Y

101 29OCT2013 04NOV2013 Y

101 05NOV2013 11NOV2013 Y

101 12NOV2013 18NOV2013 Y

101 19NOV2013 25NOV2013 Y

101 26NOV2013 02DEC2013 Y

101 03DEC2013 09DEC2013 Y

101 10DEC2013 16DEC2013 Y

101 17DEC2013 23DEC2013 Y

I applied your piece of code!!!! am i missing something?

Thanks

Sam

Re: Check the dates in loop

sam369 — Fri, 07 Nov 2014 22:57:02 GMT

Hi Arthur,

i Changed dataset in proc transpose dataset instead of have i changed to temp, Something is going wrong with this code, 101 subject doesn't have dendt is missing and dstdt :11OCT2013 but code is considering the subj 100 dendt and dstdt

proc transpose data=temp out=formerge1 prefix=_dstdt;

var dstdt;

by subjid;

run;

proc transpose data=temp out=formerge2 prefix=_dendt;

var dendt;

by subjid;

run;

data formerge;

set formerge1;

set formerge2;

run;

data want;

merge temp formerge;

by subjid;

array temp1(*) _dstdt:;

array temp2(*) _dendt:;

i=0;

do until (eof2);

set have end=eof2;

flg='N';

do i=1 to dim(temp1);

if STDT <= temp1(i) < ENDT then do;

if (temp1(i) <= temp2(i) < ENDT) or (temp2(i) >= ENDT) then flg='Y';

end;

if temp1(i) < STDT then do;

if (STDT <= temp2(i) < ENDT) or (temp2(i) > ENDT) then flg='Y';

end;

if flg eq 'Y' then leave;

end;

output;

end;

run;

output:

subjid dstdt dendt STDT ENDT flg

100 25OCT2013 17MAR2014 02OCT2013 08OCT2013 N

100 25OCT2013 17MAR2014 09OCT2013 15OCT2013 N

100 25OCT2013 17MAR2014 16OCT2013 22OCT2013 N

100 25OCT2013 17MAR2014 23OCT2013 29OCT2013 Y

100 25OCT2013 17MAR2014 30OCT2013 05NOV2013 Y

100 25OCT2013 17MAR2014 06NOV2013 12NOV2013 Y

100 25OCT2013 17MAR2014 13NOV2013 19NOV2013 Y

100 25OCT2013 17MAR2014 20NOV2013 26NOV2013 Y

100 25OCT2013 17MAR2014 27NOV2013 03DEC2013 Y

100 25OCT2013 17MAR2014 04DEC2013 10DEC2013 Y

100 25OCT2013 17MAR2014 11DEC2013 17DEC2013 Y

100 25OCT2013 17MAR2014 18DEC2013 24DEC2013 Y

100 25OCT2013 17MAR2014 25DEC2013 31DEC2013 Y

100 25OCT2013 17MAR2014 01JAN2014 07JAN2014 Y

100 25OCT2013 17MAR2014 08JAN2014 14JAN2014 Y

100 25OCT2013 17MAR2014 15JAN2014 21JAN2014 Y

100 25OCT2013 17MAR2014 22JAN2014 28JAN2014 Y

100 25OCT2013 17MAR2014 29JAN2014 04FEB2014 Y

100 25OCT2013 17MAR2014 05FEB2014 11FEB2014 Y

100 25OCT2013 17MAR2014 12FEB2014 18FEB2014 Y

100 25OCT2013 17MAR2014 19FEB2014 25FEB2014 Y

100 25OCT2013 17MAR2014 26FEB2014 04MAR2014 Y

100 25OCT2013 17MAR2014 05MAR2014 11MAR2014 Y

100 25OCT2013 17MAR2014 12MAR2014 18MAR2014 Y

100 25OCT2013 17MAR2014 19MAR2014 25MAR2014 Y

100 25OCT2013 17MAR2014 26MAR2014 01APR2014 Y

100 25OCT2013 17MAR2014 02APR2014 08APR2014 Y

101 25OCT2013 17MAR2014 24SEP2013 30SEP2013 N

101 25OCT2013 17MAR2014 01OCT2013 07OCT2013 N

101 25OCT2013 17MAR2014 08OCT2013 14OCT2013 N

101 25OCT2013 17MAR2014 05OCT2013 21OCT2013 N

101 25OCT2013 17MAR2014 22OCT2013 28OCT2013 Y

101 25OCT2013 17MAR2014 29OCT2013 04NOV2013 Y

101 25OCT2013 17MAR2014 05NOV2013 11NOV2013 Y

101 25OCT2013 17MAR2014 12NOV2013 18NOV2013 Y

101 25OCT2013 17MAR2014 19NOV2013 25NOV2013 Y

101 25OCT2013 17MAR2014 26NOV2013 02DEC2013 Y

101 25OCT2013 17MAR2014 03DEC2013 09DEC2013 Y

101 25OCT2013 17MAR2014 10DEC2013 16DEC2013 Y

101 25OCT2013 17MAR2014 17DEC2013 23DEC2013 Y

101 25OCT2013 17MAR2014 24DEC2013 30DEC2013 Y

101 25OCT2013 17MAR2014 31DEC2013 06JAN2014 Y

101 25OCT2013 17MAR2014 07JAN2014 13JAN2014 Y

101 25OCT2013 17MAR2014 14JAN2014 20JAN2014 Y

101 25OCT2013 17MAR2014 21JAN2014 27JAN2014 Y

101 25OCT2013 17MAR2014 28JAN2014 03FEB2014 Y

101 25OCT2013 17MAR2014 04FEB2014 10FEB2014 Y

101 25OCT2013 17MAR2014 11FEB2014 17FEB2014 Y

101 25OCT2013 17MAR2014 18FEB2014 24FEB2014 Y

101 25OCT2013 17MAR2014 25FEB2014 03MAR2014 Y

101 25OCT2013 17MAR2014 04MAR2014 10MAR2014 Y

101 25OCT2013 17MAR2014 11MAR2014 17MAR2014 N

101 25OCT2013 17MAR2014 18MAR2014 24MAR2014 Y

101 25OCT2013 17MAR2014 25MAR2014 31MAR2014 Y

Thanks

Sam

Re: Check the dates in loop

art297 — Fri, 07 Nov 2014 23:26:17 GMT

have and temp have to be named as shown in your example. However, it was useful to see that you treated them differently. Once you get it working the way you want, I'd change the first line of the final data step to read:

data want (drop=i _:);

Re: Check the dates in loop

art297 — Fri, 07 Nov 2014 23:55:17 GMT

My error! Here is the code I actually ran and intended to suggest:

data have;

informat STDT ENDT date9.;

format STDT ENDT date9.;

input subjid STDT ENDT flag $;

cards;

100 02OCT2013 08OCT2013 N

100 09OCT2013 15OCT2013 N

100 16OCT2013 22OCT2013 N

100 23OCT2013 29OCT2013 Y

100 30OCT2013 05NOV2013 Y

100 06NOV2013 12NOV2013 Y

100 13NOV2013 19NOV2013 Y

100 20NOV2013 26NOV2013 Y

100 27NOV2013 03DEC2013 Y

100 04DEC2013 10DEC2013 Y

100 11DEC2013 17DEC2013 Y

100 18DEC2013 24DEC2013 Y

100 25DEC2013 31DEC2013 Y

100 01JAN2014 07JAN2014 Y

100 08JAN2014 14JAN2014 Y

100 15JAN2014 21JAN2014 Y

100 22JAN2014 28JAN2014 Y

100 29JAN2014 04FEB2014 Y

100 05FEB2014 11FEB2014 Y

100 12FEB2014 18FEB2014 Y

100 19FEB2014 25FEB2014 Y

100 26FEB2014 04MAR2014 Y

100 05MAR2014 11MAR2014 Y

100 12MAR2014 18MAR2014 Y

100 19MAR2014 25MAR2014 Y

100 26MAR2014 01APR2014 Y

100 02APR2014 08APR2014 Y

101 24SEP2013 30SEP2013 N

101 01OCT2013 07OCT2013 N

101 08OCT2013 14OCT2013 Y

101 15OCT2013 21OCT2013 N

101 22OCT2013 28OCT2013 N

101 29OCT2013 04NOV2013 N

101 05NOV2013 11NOV2013 N

101 12NOV2013 18NOV2013 N

101 19NOV2013 25NOV2013 N

101 26NOV2013 02DEC2013 N

101 03DEC2013 09DEC2013 N

101 10DEC2013 16DEC2013 N

101 17DEC2013 23DEC2013 N

101 24DEC2013 30DEC2013 N

101 31DEC2013 06JAN2014 N

101 07JAN2014 13JAN2014 N

101 14JAN2014 20JAN2014 N

101 21JAN2014 27JAN2014 N

101 28JAN2014 03FEB2014 N

101 04FEB2014 10FEB2014 N

101 11FEB2014 17FEB2014 N

101 18FEB2014 24FEB2014 N

101 25FEB2014 03MAR2014 N

101 04MAR2014 10MAR2014 N

101 11MAR2014 17MAR2014 N

101 18MAR2014 24MAR2014 N

101 25MAR2014 31MAR2014 N

;

run;

data temp;

informat dstdt dendt date9.;

format dstdt dendt date9.;

input subjid dstdt dendt;

cards;

100 25OCT2013 17MAR2014

100 18MAR2014 28APR2014

101 11OCT2013 10JAN2014

101 12JAN2014 10FEB2014

;

run;

proc transpose data=temp out=formerge1 (drop=_name_) prefix=_stdt;

var dstdt;

by subjid;

run;

proc transpose data=temp out=formerge2 (drop=_name_) prefix=_endt;

var dendt;

by subjid;

run;

data formerge;

set formerge1;

set formerge2;

run;

data want (drop=i _:);

merge have formerge;

by subjid;

array temp1(*) _stdt:;

array temp2(*) _endt:;

retain temp1: temp2:;

flg='N';

do i=1 to dim(temp1);

if STDT <= temp1(i) < ENDT then do;

if (temp1(i) <= temp2(i) < ENDT) or (temp2(i) >= ENDT) then flg='Y';

end;

if temp1(i) < STDT then do;

if (STDT <= temp2(i) < ENDT) or (temp2(i) > ENDT) then flg='Y';

end;

if flg eq 'Y' then leave;

end;

output;

run;

Re: Check the dates in loop

sam369 — Sat, 08 Nov 2014 00:21:29 GMT

Hi Arthur,

Thanks for you help... I applied your code and still some glitch

I changed temp dataset which resembles to my original data

data temp;

informat dstdt dendt date9.;

format dstdt dendt date9.;

input subjid dstdt dendt;

cards;

100 25OCT2013 17MAR2014

100 18MAR2014 28APR2014

101 11OCT2013 .

;

run;

find the output

subjid STDT ENDT flag flg

100 02OCT2013 08OCT2013 N N

100 09OCT2013 15OCT2013 N N

100 16OCT2013 22OCT2013 N N

100 23OCT2013 29OCT2013 Y Y

100 30OCT2013 05NOV2013 Y Y

100 06NOV2013 12NOV2013 Y Y

100 13NOV2013 19NOV2013 Y Y

100 20NOV2013 26NOV2013 Y Y

100 27NOV2013 03DEC2013 Y Y

100 04DEC2013 10DEC2013 Y Y

100 11DEC2013 17DEC2013 Y Y

100 18DEC2013 24DEC2013 Y Y

100 25DEC2013 31DEC2013 Y Y

100 01JAN2014 07JAN2014 Y Y

100 08JAN2014 14JAN2014 Y Y

100 15JAN2014 21JAN2014 Y Y

100 22JAN2014 28JAN2014 Y Y

100 29JAN2014 04FEB2014 Y Y

100 05FEB2014 11FEB2014 Y Y

100 12FEB2014 18FEB2014 Y Y

100 19FEB2014 25FEB2014 Y Y

100 26FEB2014 04MAR2014 Y Y

100 05MAR2014 11MAR2014 Y Y

100 12MAR2014 18MAR2014 Y Y

100 19MAR2014 25MAR2014 Y Y

100 26MAR2014 01APR2014 Y Y

100 02APR2014 08APR2014 Y Y

101 24SEP2013 30SEP2013 N N

101 01OCT2013 07OCT2013 N N

101 08OCT2013 14OCT2013 Y N

101 15OCT2013 21OCT2013 N N

101 22OCT2013 28OCT2013 N N

101 29OCT2013 04NOV2013 N N

101 05NOV2013 11NOV2013 N N

101 12NOV2013 18NOV2013 N N

101 19NOV2013 25NOV2013 N N

101 26NOV2013 02DEC2013 N N

101 03DEC2013 09DEC2013 N N

101 10DEC2013 16DEC2013 N N

101 17DEC2013 23DEC2013 N N

101 24DEC2013 30DEC2013 N N

101 31DEC2013 06JAN2014 N N

101 07JAN2014 13JAN2014 N N

101 14JAN2014 20JAN2014 N N

101 21JAN2014 27JAN2014 N N

101 28JAN2014 03FEB2014 N N

101 04FEB2014 10FEB2014 N N

101 11FEB2014 17FEB2014 N N

101 18FEB2014 24FEB2014 N N

101 25FEB2014 03MAR2014 N N

101 04MAR2014 10MAR2014 N N

101 11MAR2014 17MAR2014 N N

101 18MAR2014 24MAR2014 N N

101 25MAR2014 31MAR2014 N N

Thanks

Sam

Re: Check the dates in loop

art297 — Sat, 08 Nov 2014 01:00:52 GMT

What rule do you want to use if there isn't a range (just a beginning date) in either dataset? My code simply used your original conditions!

Re: Check the dates in loop

sam369 — Sat, 08 Nov 2014 01:08:08 GMT

yes!!! beginning date. in original data beginning date is not missing for both dataset

Thanks

Sam

Re: Check the dates in loop

sam369 — Sat, 08 Nov 2014 01:13:42 GMT

Hi Arthur,

we need to stick with the original code itself

for example 101 subject dstdt is 11oct2013 and corresponding stdt & endt are "08OCT2013 14OCT2013"

it falls under this condition

if STDT <= temp1(i) < ENDT then do;

if (temp1(i) <= temp2(i) < ENDT) or (temp2(i) >= ENDT) then flg='Y';

end;

08OCT2013<= 11oct2013< 14OCT2013 then do

if 11oct2013<=.< 14OCT2013 then flg='Y'

so we need to get flg eq 'Y' but we got 'N'

Thanks

Sam

Re: Check the dates in loop

art297 — Sat, 08 Nov 2014 03:19:21 GMT

You miss the point. The code I proposed IS using your original code. However, all of your original code required a non-missing dendt in order for a flag to be set to 'Y'.

1) if STDT <= dstdt < ENDT then we need to consider flg

if dstdt <= dendt < ENDT then flg=Y;

if dendt >= ENDT then flg=Y;

2) if dstdt < STDT then we need to consider flg

if STDT <= dendt < ENDT then flg=Y;

if dendt > ENDT then flg=Y;

Re: Check the dates in loop

sam369 — Sat, 08 Nov 2014 03:44:17 GMT

Thank you for your Time!!!

Yes i am accepting that my first original post is does not have any missing dendt but my real data does!!! In my real data dstdt is not missing but few subjid dendt is missing

Thanks

Sam

Re: Check the dates in loop

art297 — Sat, 08 Nov 2014 03:53:24 GMT

But you aren't answering the question that you need to answer. What is the rule when there is a missing dendt?

You may simply want to specify that a missing enddt represents some time in the future. Howver, that has to be YOUR decision.

Re: Check the dates in loop

Ksharp — Sat, 08 Nov 2014 10:06:51 GMT

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

Re: Check the dates in loop

sam369 — Sat, 08 Nov 2014 13:18:51 GMT

Hi Keshan,

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

thanks

sam

Re: Check the dates in loop

Ksharp — Sat, 08 Nov 2014 13:59:38 GMT

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

Re: Check the dates in loop

slchen — Sat, 08 Nov 2014 15:16:14 GMT

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;