case statements with else as null

[Andreww]

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.

Thanks

Andrew

[Richard A. DeVenezia]

Post by Andreww
Hi - I have the following code in SQL which I am trying to convert to
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"

[Andreww]

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

Post by Richard A. DeVenezia

Post by Andreww
Hi - I have the following code in SQL which I am trying to convert to
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/

[BK]

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

Post by Andreww
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

Post by Richard A. DeVenezia

Post by Andreww
Hi - I have the following code in SQL which I am trying to convert to
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/

[Guillermo Ramos]

Andeww -
it depends on what type "Transactors" is:
- a number: then use "else . end as transactors"
- a string : then use "else '' end as transactors"
Regards

Post by Andreww
Hi - I have the following code in SQL which I am trying to convert to
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.
Thanks
Andrew

[Andreww]

Thanks - That's all usefull stuff!

Andrew

Post by Guillermo Ramos
Andeww -
- a number: then use "else . end as transactors"
- a string : then use "else '' end as transactors"
Regards

Post by Andreww
Hi - I have the following code in SQL which I am trying to convert to
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.
Thanks
Andrew

[Eric Eyre]

A

this is a typical task esp in financial applications -- the source data is
transaction data with multiple transaction rows per customer id, and the
analyst wants to derive the number of customers who had any transactions
between 2 dates of interest

another possibly more transparent way of expressing the logic, w/o using the
case expression, would be

select count(*) as transactors
from (select distinct customer_number from one where tran_date between
'01-may-2005' and
'01-may-2006');

Erico

Post by Andreww
Hi - I have the following code in SQL which I am trying to convert to
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.
Thanks
Andrew