```Date: Thu, 2 May 2002 08:00:20 -0700 Reply-To: "Huang, Ya" Sender: "SAS(r) Discussion" From: "Huang, Ya" Subject: Re: SQL Solution? Comments: To: "Kevin F. Spratt" 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) ________________________________________________________________ ```

Back to: Top of message | Previous page | Main SAS-L page