Date:         Wed, 19 Jul 2006 07:47:01 -0400
Reply-To:     "Richard A. DeVenezia" <rdevenezia@WILDBLUE.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Richard A. DeVenezia" <rdevenezia@WILDBLUE.NET>
Subject:      Re: case statements with else as null
Comments: To: sas-l@uga.edu

Andreww wrote:
> Hi - I have the following code in SQL which I am trying to convert to
> SAS sql in proc sql:
>
>
>    select
>          COUNT(DISTINCT
>              (CASE WHEN (tran_date BETWEEN '01-MAY-2005' AND
> '01-MAY-2006')
>     THEN customer_number
>   ELSE null
>           END)) AS transactors
>    from tblCustomer
>
>
> the idea is that a distinct count is produced when the transaction
> date is between the two dates.
>
> The piece of code I am trying to translate to SAS is the "ELSE null" -
> what do I put instead of null. doing "else missing" doesn't work.
>
> Incidentally if I comment out the "else null" the log file says values
> translated to missing which is what I want.


First concern is the dates being specified as plain strings instead of date
literals.  Is your tran_date variable a numeric containing a SAS date value
or a string containing characters that humans interpret as dates?

You can SUM a logical condition (which yields 1 for true and 0 for false).
This sample uses date literals.

SUM (tran_date BETWEEN '01MAY2005'd AND '01MAY2006'd) as nTransactions label
= "Number Of Transactions In Date Range Of Pointy Haired Interest"

--
Richard A. DeVenezia
http://www.devenezia.com/