LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (May 2002, week 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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?
Comments: To: "Kevin F. Spratt" <kevin-spratt@UIOWA.EDU>
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