Hi,
Thanks for your reply. I have listed the information in excel sheet to make it more clear. So sent table has all the sent email information. Open table tracked people opening the emails we sent. Complain table has the records of users complaining about our sent emails. This is my code here, it worked perfectly fine when I am only joining two tables, however giving extra wrong records when I am joining more than 2 tables...
**proc sql;
create table result_test as
select bounce.bouncedate,
open.openflag,
open.opendate,
complain.complainflag,
complain.complaindate,
coalesce (sent.JobId, open.JobId, bounce.JobId, complain.JobID) as JobId
, coalesce (sent.EmailAddress, open.EmailAddress, bounce.EmailAddress, complain.EmailAddress) as EmailAddress
, case
when bounce.JobID is null or bounce.EmailAddress is null then '0'
else bounce.bounceflag
end as bounceflag
,case when open.JobID is null or open.EmailAddress is null then '0'
else open.openflag end as openflag,
case when complain.JobID is null or complain.EmailAddress is null then'0'
else complain.complainflag
end as complainflag
from
sent
full join
bounce
on
sent.JobId = bounce.JobId AND
sent.EmailAddress = bounce.EmailAddress
full join
open on
sent.JobId = open.JobId AND
sent.EmailAddress = open.EmailAddress
full join
complain on
sent.JobID= complain.JobID AND
sent.EmailAddress= complain.EmailAddress
;**
(this is the wrong extra record example. As you can see there shouldn't be two same complaindates..
... View more