BookmarkSubscribeRSS Feed
pp2014
Fluorite | Level 6

I have following data with company drugs as well as competitors drugs under the market at hcp_id level for 3 months.

I am showing one market_id and one hcp id as an example.

data have;
input hcp_id $3. mkt_id $2. prd_id $2. mkt_trx1-mktrx3 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

 

I have list of company product under the market id 12:


data cmpny_prod;
input mkt_id $2. prd_id $2.;
cards;
12 8
12 9


I want the output below that includes prd_id (company products) from list under market 12 (if they are not in the have data) with 0
trxs for 3 months under the market id 12 along with other competitors products.

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 1 1
123 12 8 1 3 2 0 0 0
123 12 9 1 3 2 0 0 0

8 REPLIES 8
Ksharp
Super User

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;
pp2014
Fluorite | Level 6

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

 

 

Ksharp
Super User

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;
PGStats
Opal | Level 21

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.

PG
pp2014
Fluorite | Level 6

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..

 

PGStats
Opal | Level 21

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;
PG
pp2014
Fluorite | Level 6

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;

Ksharp
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1012 views
  • 0 likes
  • 3 in conversation