Xihu,

In case others might be interested, I reply it publicly:

In PROC SQL, "HAVING" statement is very powerful in terms of sub-setting dataset based on some kind of criteria, it differs it from "WHERE" in that it can be used to keep all the "GROUP BY" records even if only part of the records in the group meet the criteria. While "WHERE" usually are used to filter out only those obs that indeed meet the requirement.

In your case, the criteria can be translated as: 1. "Each id can have a number of records" -> the number of records can be obtained by count() function, note, this is count by row. 2. "All three field must equals 98" -> t1=98 and t2=98 and t3=98, or (t1=98)+(t2=98)+(t3=98)=3. note, this is a sum by column within one record. 3. "All records meet 2." -> total records x 3 = total number of all the three fields in all the records in the group. note, sum() give you the total by row.

therefore the "HAVING" shoud be:

having count(id)*3=sum((t1=98)+(t2=98)+(t3=98)); or having count(id)=sum(t1=98 and t2=98 and t3=98); (this should be better)

This code can be easily modified to meet other criteria. Suppose we want to filter out the id group if at least 2 of the records and no more than 4 of the records in the group have all fields=98:

having 2<=sum(t1=98 and t2=98 and t3=98)<=4;

I hope I have make it clear.

Regards,

Ya Huang

-----Original Message----- From: xihu lu [mailto:xihulu@yahoo.com] Sent: Friday, November 17, 2000 9:50 AM To: Huang, Ya Subject: RE: retrieving records

Hi,

Of the several responses I obtained, your is the ONLY one that worked. Thank you very much.

I wonder if you would mind explaining the following line:

having count(id)*3=sum((t1=98)+(t2=98)+(t3=98))

Xihu

--- "Huang, Ya" <ya.huang@Agouron.COM> wrote: > Xihu, > > See the following: > > data xx; > input id t1 t2 t3; > cards; > 1 23 42 15 > 1 98 98 98 > 2 30 21 45 > 2 34 33 45 > 2 98 98 98 > 3 98 98 98 > 3 98 98 98 > 4 98 98 98 > 4 98 98 98 > ; > > options nocenter; > proc sql; > select * > from xx > group by id > having count(id)*3=sum((t1=98)+(t2=98)+(t3=98)) > ; > > --------------------------- > > The SAS System 09:24 Friday, November 17, > 2000 2 > > id t1 t2 t3 > ------------------------------------------ > 3 98 98 98 > 3 98 98 98 > 4 98 98 98 > 4 98 98 98 > > > Regards, > > Ya Huang > > > -----Original Message----- > From: xihu lu [mailto:xihulu@YAHOO.COM] > Sent: Friday, November 17, 2000 9:11 AM > To: SAS-L@LISTSERV.UGA.EDU > Subject: retrieving records > > > Hello there, > > The following is the dataset in question: > > > id t1 t2 t3 > 1 23 42 15 > 1 98 98 98 > 2 30 21 45 > 2 34 33 45 > 2 98 98 98 > 3 98 98 98 > 3 98 98 98 > 4 98 98 98 > 4 98 98 98 > > > Each id can have a number of records. I'd like to > find > out > how many ids have a value of '98' for all records > under all > three fields (i.e. t1, t2, t3). In this case, the > output > will be : > > 3 98 98 98 > 3 98 98 98 > 4 98 98 98 > 4 98 98 98 > > > Could someone help me achieve this? Many thanks. > > > Xihu > > > __________________________________________________ > Do You Yahoo!? > Yahoo! Calendar - Get organized for the holidays! > http://calendar.yahoo.com/ >

__________________________________________________ Do You Yahoo!? Yahoo! Calendar - Get organized for the holidays! http://calendar.yahoo.com/