Date: Thu, 2 May 2002 08:00:20 -0700
Reply-To: "Huang, Ya" <ya.huang@PFIZER.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Huang, Ya" <ya.huang@PFIZER.COM>
Subject: Re: SQL Solution?
Content-Type: text/plain
Sorry, didn't read your last sentence.
Here is a revised version, it will pick
the maximum number of trials:
proc sql;
select *
from (select *, count(distinct trial) as nt
from xx
group by id)
having nt=max(nt)
;
-----Original Message-----
From: Huang, Ya
Sent: Thursday, May 02, 2002 7:52 AM
To: 'Kevin F. Spratt'; SAS-L@LISTSERV.UGA.EDU
Subject: RE: SQL Solution?
proc sql;
select *
from yourdata
group by id
having count(distinct trial)=3
;
-----Original Message-----
From: Kevin F. Spratt [mailto:kevin-spratt@UIOWA.EDU]
Sent: Thursday, May 02, 2002 7:47 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: SQL Solution?
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.
________________________________________________________________
_____________
Kevin F. Spratt, Ph.D. | |
Iowa Testing Programs (ITP) ( )
University of Iowa Back Care (UIBC) \ \_ University
Iowa Spine Research Center (ISRC) \ * \ of Iowa
224-D Lindquist Center \ / Hawkeyes
The University of Iowa |____________ )
Iowa City, Iowa 52242 V
(319) 335-5572 (voice)
(319) 335-6399 (fax)
(319 530-8254 (cell & Voice mail)
Kevin-Spratt@Uiowa.edu (e-mail)
________________________________________________________________
