Date: Wed, 19 Jul 2006 04:55:49 -0700
Reply-To: Andreww <andrew.whittam@GMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Andreww <andrew.whittam@GMAIL.COM>
Subject: Re: case statements with else as null
Content-Type: text/plain; charset="iso-8859-1"
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".
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