topic Re: case when coalesce with max function? in SAS Programming

case when coalesce with max function?

teddyee — Sat, 24 Jun 2017 08:32:23 GMT

Table 1
CustID, date contact, filter
101, 1-Jan-17, Y
102, 2-Jan-17, N
103, 3-Jan-17, A
104, 4-Jan-17, A

Table 2
CustID, date contact, filter
101, 2-Jan-17, N
102, 2-Jan-17, Y
103,
104, 4-Jan-17, N

Result
CustID ,date contact ,filter
101, 2-Jan-17, Y
102, 2-Jan-17, Y
103, 3-Jan-17, A
104, 4-Jan-17, N

CASE 
 WHEN coalesce (1.date_contact,2.date_contact) is not null then max (1.date_contact,2.date_contact)
 END AS date_contact FORMAT DATE9.

CASE 
 WHEN coalesce (1.filter,2.filter) is not null then max (1.filter,2.filter)
 END AS filter

Question: i can get the latest date for date_contact when comparing table 1 and 2 but why not for filter??

my result for filter is y>n>a>null

Re: case when coalesce with max function?

ChrisNZ — Sat, 24 Jun 2017 09:41:50 GMT

1-Why the duplication? See https://communities.sas.com/t5/forums/editpage/board-id/general_program/message-id/47215

2-No need for coalesce() as SAS will ignore missing values (there are no null values in SAS)

Re: case when coalesce with max function?

teddyee — Sat, 24 Jun 2017 10:29:41 GMT

Hi ChrisNZ, unable to open the link, able to send again. thanks

Re: case when coalesce with max function?

ChrisNZ — Sat, 24 Jun 2017 10:38:10 GMT

that's the link to your other, similar, question

https://communities.sas.com/t5/General-SAS-Programming/case-when-coalesce-with-max-function/m-p/370247