Date:         Wed, 12 Mar 2008 23:43:10 -0400
Reply-To:     Ya Huang <ya.huang@AMYLIN.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Ya Huang <ya.huang@AMYLIN.COM>
Subject:      Re: Logic behind this query???
Comments: To: ali6058@GMAIL.COM
Content-Type: text/plain; charset=ISO-8859-1

proc sql;
select *
 from (select *, loc^=max(case when visit=0 then loc else '' end) as loc0
       from test group by patno, grp)
group by patno, grp
having loc0 or (visit=0 and grp=max(case when loc0 then grp else . end))
;

  patno     visit  loc       Date           grp      loc0
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
    123         1  nmk       03/10/06         2         1
    123         0  yat       12/10/05         2         0
    127         1  qqq       10/20/06         3         1
    127         0  mll       09/20/05         3         0


On Wed, 12 Mar 2008 19:33:15 -0700, Al <ali6058@GMAIL.COM> wrote:

>Hi,
>
>   This is the dataset i have
>
>
>  data test;
>   input patno visit loc $  Date $ grp;
>   cards;
>                                123 0 xyz 12/10/05 1
>                123 0 yat 12/10/05 2
>                123 1 xyz 02/10/06 1
>                123 1 nmk 03/10/06 2
>                123 3 xyz 03/10/06 1
>                123 3 yat 11/25/05 2
>                127 0 abc 09/20/05 1
>                127 0 xhx 09/20/05 2
>                127 0 mll 09/20/05 3
>                127 1 abc 10/20/06 1
>                127 1 xhx 10/20/06 2
>                127 1 qqq 10/20/06 3
>                ;
>        run;
>
>
>I am trying to print those values of loc where values of loc at visit
>0 does not match with value of loc at visit X,  within grp .
>
>   The output would be
>
>   patno visit loc   date     grp
>   123   0     yat  12/10/05   2
>   123   1     nmk  03/10/06   2
>   127   0     mll  09/20/05   3
>   127   1     qqq  10/20/06   3
>
>   I hope i am clear in explaining the problem..
>
>can you help me with the solution
>
>Thanks in advace
>
>
>   Thanks