case when coalesce with max function?

Reply
Occasional Contributor
Posts: 8

case when coalesce with max function?

 

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

PROC Star
Posts: 1,669

Re: case when coalesce with max function?

[ Edited ]

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)

Occasional Contributor
Posts: 8

Re: case when coalesce with max function?

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

PROC Star
Posts: 1,669

Re: case when coalesce with max function?

Ask a Question
Discussion stats
  • 3 replies
  • 136 views
  • 0 likes
  • 2 in conversation