last attempt:
data have;
input hcp_id :$3. mkt_id :$2. prd_id :$2. mkt_trx1-mkt_trx3 2. prd_trx1-prd_trx3 2.;
datalines;
123 12 4 1 3 2 0 2 1
123 12 5 1 3 2 1 1 1
124 12 1 3 2 1 3 2 1
;
data cmpny_prod;
input mkt_id :$2. prd_id :$2.;
datalines;
12 5
12 8
12 9
;
proc sql;
create table want as
select b.hcp_id, b.mkt_id, a.prd_id,
b.mkt_trx1, b.mkt_trx2, b.mkt_trx3,
coalesce(c.prd_trx1, 0) as prd_trx1,
coalesce(c.prd_trx2, 0) as prd_trx2,
coalesce(c.prd_trx3, 0) as prd_trx3
from
( select hcp_id, mkt_id, prd_id from have
union
select hcp_id, mkt_id, prd_id from (select hcp_id from have), cmpny_prod ) as a inner join
(select unique hcp_id, mkt_id, mkt_trx1, mkt_trx2, mkt_trx3 from have) as b
on a.hcp_id=b.hcp_id and a.mkt_id=b.mkt_id left join
have as c
on b.hcp_id=c.hcp_id and b.mkt_id=c.mkt_id and a.prd_id=c.prd_id and
b.mkt_trx1=c.mkt_trx1 and b.mkt_trx2=c.mkt_trx2 and b.mkt_trx3=c.mkt_trx3;
select * from want;
quit;
... View more
