Date:         Fri, 29 Aug 2008 08:59:00 -0400
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>"
              <schreier.junk.mail@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>"
              <schreier.junk.mail@GMAIL.COM>
Subject:      Re: a query

On Thu, 28 Aug 2008 22:35:23 -0700, shashi <shashi2707@GMAIL.COM> wrote:

>Hi,
>Here I have a query. My data looks like
>
>patientid visit
>1          0
>1          4
>2          0
>1          1
>2          1
>2          3
>7          3
>3          0
>4          1
>1          2
>5          2
>4          0
>7          0
>1          3
>
>
>From this I want to have the patientids having only visit '0'.
>
>that is,
>I want the resultant dataset as
>
>patientid   visit
>3            0
>
>Could anybody suggest on this?

On Thu, 28 Aug 2008 22:58:24 -0700, Sandip <sandiptoton@GMAIL.COM> wrote:

>Hi Shashi,
>
>You can try this code.
>
>data test;
>input patientid visit;
>cards;
>1          0
>1          4
>2          0
>1          1
>2          1
>2          3
>7          3
>3          0
>4          1
>1          2
>5          2
>4          0
>7          0
>1          3
>;
>
>proc sql;
>create table Out as
>select patientid, sum(visit) as sumvisit
>from Test
>group by patientid
>having calculated sumvisit =0;
>quit;
>
>
>Regards,
>Sandip

Or simply

   select *
    from Test
    group by patientid
    having sum(visit) =0;