Why you need SQL code ? There are many thing you need to consider about . The following can give you a start.
data have;
input hcp_id : $3. mkt_id : $2. prd_id :$2. mk_trx1-mk_trx3 2. prd_trx1-prd_trx3 2.;
cards;
123 12 4 1 3 2 0 2 1
123 12 5 1 3 2 1 1 1
;
run;
data cmpny_prod;
input mkt_id :$2. prd_id : $2.;
cards;
12 8
12 9
;
run;
proc sql;
create table want as
select
max(hcp_id) as hcp_id,
mkt_id,
prd_id,
max(mk_trx1) as mk_trx1,
max(mk_trx2) as mk_trx2,
max(mk_trx3) as mk_trx3,
coalesce(prd_trx1,0) as prd_trx1,
coalesce(prd_trx2,0) as prd_trx2,
coalesce(prd_trx3,0) as prd_trx3
from(
select * from have
outer union corr
select * from cmpny_prod
)
group by mkt_id
order by mkt_id,prd_id
;
quit;
Thanks Ksharp forthe solution But if I have prd_id from cmpny_prod already exists in the have data then I do not want trx1-trx3 to be 0.
For example:
data have;
input hcp_id : $3. mkt_id : $2. prd_id :$2. mk_trx1-mk_trx3 2. prd_trx1-prd_trx3 2.;
cards;
123 12 4 1 3 2 0 2 1
123 12 5 1 3 2 1 0 1
123 12 8 1 3 2 0 1 0
;
run;
I want the ouput to be as follows(since prd_id = 8 already exists in have):
Output should be as follows:
hcp_id mkt_id prd_id mkt_trx1 mkt_trx2 mkt_trx3 prd_trx1 prd_trx2 prd_trx3
123 12 4 1 3 2 0 2 1
123 12 5 1 3 2 1 0 1
123 12 8 1 3 2 0 1 0
123 12 9 1 3 2 0 0 0
I know there are many scenarios you need consider about . Your post is ambiguous .
data have;
input hcp_id : $3. mkt_id : $2. prd_id :$2. mk_trx1-mk_trx3 2. prd_trx1-prd_trx3 2.;
cards;
123 12 4 1 3 2 0 2 1
123 12 5 1 3 2 1 0 1
123 12 8 1 3 2 0 1 0
;
run;
data cmpny_prod;
input mkt_id :$2. prd_id : $2.;
cards;
12 8
12 9
;
run;
proc sql;
create table want as
select
max(hcp_id) as hcp_id,
mkt_id,
prd_id,
max(mk_trx1) as mk_trx1,
max(mk_trx2) as mk_trx2,
max(mk_trx3) as mk_trx3,
coalesce(prd_trx1,0) as prd_trx1,
coalesce(prd_trx2,0) as prd_trx2,
coalesce(prd_trx3,0) as prd_trx3
from(
select * from have
outer union corr
select * from cmpny_prod where catx(' ',mkt_id,prd_id) not in ( select catx(' ',mkt_id,prd_id) from have)
)
group by mkt_id
order by mkt_id,prd_id
;
quit;
I would suggest
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
;
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 mkt_id, prd_id from have
union
select mkt_id, prd_id from cmpny_prod ) as a inner join
(select unique hcp_id, mkt_id, mkt_trx1, mkt_trx2, mkt_trx3 from have) as b
on 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;
it looks messy, but it works.
Thanks PG Stats..Your solution almost works except that I want ptrd_trx1-prd_trx3 to be 0 for the products in the cmpny_prod dataset only if they are not in have. So If I have another hcp_id added to have, then according to your logic I am getting wrong results..
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;
Thanks Ksharp.. Your solution works only if I have only one hcp_id. If I have multiple hcp_ids like below, it does not give me right solution...
data have;
input hcp_id : $3. mkt_id : $2. prd_id :$2. mk_trx1-mk_trx3 2. prd_trx1-prd_trx3 2.;
cards; 123 12 4 2 4 2 0 2 1
123 12 5 2 4 2 1 0 1
123 12 8 2 4 2 0 1 0
123 12 9 2 4 2 1 1 0
124 12 1 3 2 1 3 2 1
;
run;
data cmpny_prod;
input mkt_id :$2. prd_id : $2.;
cards;
12 8
12 9 ;
run;
Ou.You are trying to do Cartesian Product ! I think it is better for data step .
data have;
input hcp_id : $3. mkt_id : $2. prd_id :$2. mk_trx1-mk_trx3 2. prd_trx1-prd_trx3 2.;
cards; 123 12 4 2 4 2 0 2 1
123 12 5 2 4 2 1 0 1
123 12 8 2 4 2 0 1 0
123 12 9 2 4 2 1 1 0
124 12 1 3 2 1 3 2 1
;
run;
data cmpny_prod;
input mkt_id :$2. prd_id : $2.;
cards;
12 8
12 9
;
run;
proc sql;
create table temp as
select *
from (select distinct hcp_id from have),(select distinct mkt_id,prd_id from cmpny_prod);
create table want as
select
hcp_id,
mkt_id,
prd_id,
max(mk_trx1) as mk_trx1,
max(mk_trx2) as mk_trx2,
max(mk_trx3) as mk_trx3,
coalesce(prd_trx1,0) as prd_trx1,
coalesce(prd_trx2,0) as prd_trx2,
coalesce(prd_trx3,0) as prd_trx3
from(
select * from have
outer union corr
select * from temp where catx(' ',hcp_id,mkt_id,prd_id) not in ( select catx(' ',hcp_id,mkt_id,prd_id) from have)
)
group by hcp_id,mkt_id
order by hcp_id,mkt_id,prd_id
;
quit;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.