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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.