topic Re: Help with the data using PROC SQL in SAS Programming

Help with the data using PROC SQL

pp2014 — Fri, 05 Feb 2016 01:51:38 GMT

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

I would appreciate any help in this..

Re: Help with the data using PROC SQL

Ksharp — Fri, 05 Feb 2016 02:31:26 GMT

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;

Re: Help with the data using PROC SQL

pp2014 — Fri, 05 Feb 2016 03:02:58 GMT

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

Re: Help with the data using PROC SQL

Ksharp — Fri, 05 Feb 2016 03:32:23 GMT

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;

Re: Help with the data using PROC SQL

PGStats — Fri, 05 Feb 2016 03:56:42 GMT

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.

Re: Help with the data using PROC SQL

pp2014 — Fri, 05 Feb 2016 03:58:57 GMT

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;

Re: Help with the data using PROC SQL

pp2014 — Fri, 05 Feb 2016 04:09:22 GMT

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

Re: Help with the data using PROC SQL

Ksharp — Fri, 05 Feb 2016 07:35:18 GMT

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;

Re: Help with the data using PROC SQL

PGStats — Sun, 07 Feb 2016 02:31:38 GMT

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;