Date:         Wed, 19 Jul 2006 05:54:59 -0700
Reply-To:     BK <byronkirby@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         BK <byronkirby@GMAIL.COM>
Organization: http://groups.google.com
Subject:      Re: case statements with else as null
Comments: To: sas-l@uga.edu
In-Reply-To:  <1153310149.311004.236490@p79g2000cwp.googlegroups.com>
Content-Type: text/plain; charset="iso-8859-1"

the ELSE critera is not mandatory so I would just place the "END" after
the "Customer_number".  You will however get "NOTE: A CASE expression
has no ELSE clause. Cases not accounted for by the WHEN clauses will
result in a missing value for the CASE expression." in printed into
your log.


COUNT( DISTINCT
(CASE WHEN
 (tran_date BETWEEN '01-MAY-2005' AND '01-MAY-2006') THEN
customer_number END)) AS transactors


Andreww wrote:
> Richard - Thanks for that.  The code was copied from memory and the
> dates are actually set as macro vars anyway... all that side is fine.
>
> I probably hadn't been very clear, but what I'm after is what to put
> instead of "else null".
>
> Cheers
>
> Andrew
>
>
> Richard A. DeVenezia wrote:
> > 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/