DATA Step, Macro, Functions and more

Help with the data using PROC SQL

Reply
Frequent Contributor
Posts: 124

Help with the data using PROC SQL

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

Super User
Posts: 9,671

Re: Help with the data using PROC SQL

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;
Frequent Contributor
Posts: 124

Re: Help with the data using PROC SQL

[ Edited ]

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

 

 

Super User
Posts: 9,671

Re: Help with the data using PROC SQL

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;
Respected Advisor
Posts: 4,641

Re: Help with the data using PROC SQL

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
Frequent Contributor
Posts: 124

Re: Help with the data using PROC SQL

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

 

Respected Advisor
Posts: 4,641

Re: Help with the data using PROC SQL

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
Frequent Contributor
Posts: 124

Re: Help with the data using PROC SQL

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;

Super User
Posts: 9,671

Re: Help with the data using PROC SQL

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;
Ask a Question
Discussion stats
  • 8 replies
  • 328 views
  • 0 likes
  • 3 in conversation