Date:         Wed, 2 Jul 2003 20:08:35 -0400
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: proc sql
Comments: To: Kristie Beth <kristie_35_89@YAHOO.COM>
Content-Type: text/plain

Your WHERE condition controls the selection of rows of data. In this case
the query will not include a row in a group if both target and target2 have
missing values. This means that either one or the other of the variables can
still have missing values, but not both. Now you want to select the minimum
date when the target variable is not missing. In the example that follows,
the CASE clause assigns the minimum of dates in the group when target is not
missing, else another value (a numeric missing will work if date is a
numeric type of variable):
proc sql,
select distinct patid,
case when target is not missing then min(date)
                                else . /* or whatever */
end as minDate
from dataset
where target is not missing or target2 is not missing
group by patid
order by patid
quit;

For different requirements you might prefer the COALESCE() function. It
works like a CASE statement for the special purpose of selecting the first
non-missing value in an argument list. For example, COALESCE(target,
target2) would select the value of target if not missing else target2.

Note that I have added the qualifier DISTINCT to the SELECT statement. Your
data table (dataset) may have the same minimum date value in two different
rows within a group. The DISTINCT qualifier eliminates duplicate rows (at
the price of sorting the yield of the query, though it will do so anyway to
implement the ORDER clause).

Sig


-----Original Message-----
From: Kristie Beth
To: SAS-L@LISTSERV.UGA.EDU
Sent: 7/2/2003 2:45 PM
Subject: proc sql

Hi, I have another question (thanks so much for the other help!), this
is on proc sql, I am not understanding why or HOW the 'case when' works

For example, if I have the following code,

proc sql,
select patid,
case
when target is not missing then min(date)

from dataset
where target is not missing or target2 is not missing
group by patid
order by patid
quit;

What I want to get is the min. date when the variable target is not
missing but this code  I have written doesn't achieve this.  What's
wrong?  Also, I am trying to get the minimum of multiple variables, not
just one but all have the condition of where target is not missing OR
target2 is not missing.

I have read the documentation thru and thru on proc sql but still, the
conditional statements seem to a problem...

thanks, Kristie


---------------------------------
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.