Date:         Wed, 12 Dec 2007 14:27:41 -0600
Reply-To:     Yu Zhang <zhangyu05@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Yu Zhang <zhangyu05@GMAIL.COM>
Subject:      Re: FW: Re: find consecutive dates-debug
In-Reply-To:  <B84879D70E8C1C418A6C8DA90F733134E30B3E@A-EXCH-VS1.amylin.com>
Content-Type: text/plain; charset=ISO-8859-1

One more Try, let me know if it is not working!

*

data* find1;

input ptid dai strtdt diff ;

datalines;

001 3 3 0

001 3 4 -0.5

001 3 5 0

001 3 6 0

001 3 7 -0.5

001 3 9 0

001 3 10 0

001 3 11 0

001 3 12 0

001 3 13 0

001 3 14 -0.5

001 3 15 -0.5

001 3 16 -0.5

001 3 17 0

001 3 18 0

001 3 19 0

001 3 20 0

001 3 21 0

001 3 22 0

001 3 23 0

001 3 27 0

001 3 28 0

001 3 29 0

001 5 3 -0.3

001 5 4 0

001 5 5 0

001 5 6 0

001 5 7 0

001 5 8 0

001 5 9 0

001 5 10 0

001 5 11 0

001 5 14 -0.5

001 5 15 -0.5

001 5 16 -0.5

001 5 17 0

001 5 18 0

001 5 19 0

001 5 22 0

001 5 23 0

001 5 25 1

001 5 26 -.5

;


*

proc* *sort* data= find1;

by ptid dai strtdt;

*

data* desd2;

call missing(many,begin);

do until (last.ptid );

set find1;

by ptid dai strtdt;

n+*1*;

if diff>=*0* then do;

if missing(begin) then begin = strtdt;

if strtdt=sum(begin,many) then many+*1*;

else do; begin=strtdt;link outdata; many=*1*; end;

end;

else

do;

link outdata; call missing(many,begin);

end;

if last.ptid then do;n+*1*;link outdata;end;

end;

return;

outdata:

*put many= ptid= dai= diff= strtdt= n=;

if many>=*2* then do p=n-many to n-*1* by *1*;

set find1 point=p;

*put begin= ptid= many= dai= diff=;

output;

end;

return;
*

run*;


On Dec 12, 2007 2:15 PM, Huang, Ya <Ya.Huang@amylin.com> wrote:

> Tailan,
>
> The assumption of my code is that all diff >=0 has been set to 0 (as
> your sample code showed), so that all the 0s can be treat as a group. If
> not, it can be done very easily with a dummy var. Another assumption is
> that the strtdt is consecutive without a hole in it, if there is a hole
> (like 23 jump to 27), I need to reset the counter for grp, this can be
> done too.
>
> The modified code:
>
> data find1;
> input ptid dai strtdt diff;
> diff1=(diff >=0);
>  datalines;
> 001 3 3 0
> 001 3 4 -0.5
> 001 3 5 0
> 001 3 6 0
> 001 3 7 -0.5
> 001 3 9 0
> 001 3 10 0
> 001 3 11 0
> 001 3 12 0
> 001 3 13 0
> 001 3 14 -0.5
> 001 3 15 -0.5
> 001 3 16 -0.5
> 001 3 17 0
> 001 3 18 0
> 001 3 19 0
> 001 3 20 0.5
> 001 3 21 0.6
> 001 3 22 0.7
> 001 3 23 0
> 001 3 27 0
> 001 3 28 0
> 001 3 29 0
> 001 5 3 -0.3
> 001 5 4 0
> 001 5 5 0
> 001 5 6 0
> 001 5 7 2.5
> 001 5 8 0
> 001 5 9 0
> 001 5 10 0
> 001 5 11 0
> 001 5 14 -0.5
> 001 5 15 -0.5
> 001 5 16 -0.5
> 001 5 17 0
> 001 5 18 0
> 001 5 19 0
> 001 5 20 0
> 001 5 22 0
> 001 5 27 0
> 001 5 29 0
> ;
>
>
> data find2;
>  set find1;
> by ptid dai diff1 notsorted;
> if first.diff1 or strtdt ^=lag(strtdt)+1 then grp+1;
> run;
>
> proc sql;
> create table find3 as
> select *
> from find2
> group by ptid,dai,grp
> having count(grp) >=7 and max(diff1)=1
> order by ptid,dai,strtdt
> ;
>
>  ptid    dai    strtdt    diff    diff1    grp
>
>   1      3       17       0.0      1        7
>   1      3       18       0.0      1        7
>   1      3       19       0.0      1        7
>   1      3       20       0.5      1        7
>   1      3       21       0.6      1        7
>   1      3       22       0.7      1        7
>   1      3       23       0.0      1        7
>   1      5        4       0.0      1       10
>   1      5        5       0.0      1       10
>   1      5        6       0.0      1       10
>   1      5        7       2.5      1       10
>   1      5        8       0.0      1       10
>   1      5        9       0.0      1       10
>   1      5       10       0.0      1       10
>   1      5       11       0.0      1       10
>
> ________________________________
>
> From: Tailan Jing [mailto:jingtailan@gmail.com]
> Sent: Wednesday, December 12, 2007 11:51 AM
> To: Huang, Ya
> Subject: Re: Re: find consecutive dates-debug
>
>
>
> Ya:
>
> If DIFF has not only 0 but also diff> = 0, your code is not working.
> please advise. tks a lot.
>
>
> ------------------------------------------------------------------------
> -----------------------------------------------------------------
> data find1;
> input ptid dai strtdt diff ;
> datalines;
> 001 3 3 0
> 001 3 4 -0.5
> 001 3 5 0
> 001 3 6 0
> 001 3 7 -0.5
> 001 3 9 0
> 001 3 10 0
> 001 3 11 0
> 001 3 12 0
> 001 3 13 0
> 001 3 14 -0.5
> 001 3 15 -0.5
> 001 3 16 -0.5
> 001 3 17 0
> 001 3 18 0
> 001 3 19 0
> 001 3 20 0.5
> 001 3 21 0.6
> 001 3 22 0.7
> 001 3 23 0
> 001 3 27 0
> 001 3 28 0
> 001 3 29 0
> 001 5 3 -0.3
> 001 5 4 0
> 001 5 5 0
> 001 5 6 0
> 001 5 7 2.5
> 001 5 8 0
> 001 5 9 0
> 001 5 10 0
> 001 5 11 0
> 001 5 14 - 0.5
> 001 5 15 -0.5
> 001 5 16 -0.5
> 001 5 17 0
> 001 5 18 0
> 001 5 19 0
> 001 5 20 0
> 001 5 22 0
> 001 5 27 0
> 001 5 29 0
> ;
>
>
> data find2;
>  set find1;
> by ptid dai diff notsorted;
> if first.diff then grp+1;
> run;
>
> proc sql;
> create table find3 as
> select *
> from find2
> group by ptid,dai,grp
> having count(grp) >=7 and max(diff)=0  /*??????? */
> order by ptid,dai,strtdt
> ;
>