Date:         Thu, 2 May 2002 11:14:04 -0400
Reply-To:     "Buchanan, Gordon" <gordon.buchanan@GMACRFC.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Buchanan, Gordon" <gordon.buchanan@GMACRFC.COM>
Subject:      Re: SQL Solution?

Here's one possible solution. Assumes that there is only one row per trial
per id.

proc sql;
  select count(distinct trial)
  into :n
  from a;

  create table b as
  select id, count(*)
  from a
  group by id
  having count(*)=&n;

  select a.*
  from a,b
  where a.id=b.id;
  quit;


On Thu, 2 May 2002 09:46:54 -0500, Kevin F. Spratt <kevin-spratt@UIOWA.EDU>
wrote:

>I can solve this using a data step approach but suspect an
>SQL approach would be simpler.
>
>Suppose you have a data set with multiple records per subject and
>want to include only those subjects who have at least some
>data for all possible records.
>
>For example:
>
>id trial x y z
>1     1  1 2 3
>1     2  2 3 4
>1     3  4 . 1
>2     1  1 3 2
>2     3  3 3 2
>3     1  1 1 2
>3     2  1 1 2
>3     3  . 1 3
>
>My desired solution would be:
>1     1  1 2 3
>1     2  2 3 4
>1     3  4 . 1
>3     1  1 1 2
>3     2  1 1 2
>3     3  . 1 3
>
>It would also be nice to solve this in the general case where I
>wouldn't need to know that the data set has 3 trials per subject.