Hi,
I have a table of claims data by ID and medical condition.
ID MC ServiceDate ProductID
1 ABC 2014-01-01 123
1 DEF 2014-02-01 456
1 ABC 2014-03-01 222
1 DEF 2014-04-01 333
I also have a mapping table where ProductID is mapped to multiple 'preferred' products
NP_ProductID Preferred_ProductID
123 222
123 223
123 224
456 333
456 334
456 335
etc.
The objective is to understand whether a patient is switching to a 'preferred' product.
I am having troubles trying to figure how to 'look up' whether a patient takes a preferred product at a later period in their data and count it as a switch.
The output table I am trying to achieve is:
ID MC Switch_Cnt
1 ABC 1
1 DEF 1
I think the approach is to use a hash table, but I am having troubles figuring out the logic and code
Thanks in advance for your help.
Can be done with a three-way join:
proc sql;
create table switches as
select a.ID, a.MC, a.productID as from_productID, c.productID as to_productID
from
have as a inner join
map as b on a.productID=b.NP_productID inner join
have as c on a.id=c.id and a.MC=c.MC and
a.serviceDate<c.serviceDate and b.preferred_productID=c.productID
order by ID, MC, from_productID, to_productID;
select * from switches;
quit;
After you get the list of product switches, It is only a matter of counting whatever you want to count.
PG
data have;
input ID MC $ ServiceDate : yymmdd10. ProductID;
format ServiceDate yymmdd10.;
cards;
1 ABC 2014-01-01 123
1 DEF 2014-02-01 456
1 ABC 2014-03-01 222
1 DEF 2014-04-01 333
;
run;
data map;
input NP_ProductID Preferred_ProductID;
cards;
123 222
123 223
123 224
456 333
456 334
456 335
;
run;
proc sort data=have ;by MC;run;
data temp;
merge have have(firstobs=2 keep=MC ProductID rename=(MC=_MC ProductID=_ProductID));
run;
data want;
if _n_ eq 1 then do;
if 0 then set map;
declare hash h(dataset:'map');
h.definekey('NP_ProductID','Preferred_ProductID');
h.definedone();
end;
set temp;
by MC;
if first.MC then n=0;
if MC=_MC and h.check(key:ProductID ,key:_ProductID)=0 then n+1;
if last.MC;
keep id MC n;
run;
Thanks for the reply xia.
I find that this solution works if it only looks 1 record ahead, but if the data had more purchases, how would i be able to capture whether each non preferred product has a later corresponding 'preferred' product?
for example: a patient may have purchased 3 alternatives to 456.
data have;
input ID MC $ ServiceDate : yymmdd10. ProductID;
format ServiceDate yymmdd10.;
cards;
1 ABC 2014-01-01 123
1 DEF 2014-02-01 456
1 ABC 2014-03-01 222
1 DEF 2014-04-01 333
1 DEF 2014-05-01 335
1 DEF 2014-05-12 456
1 DEF 2014-05-15 334
;
run;
Do you want check all the combination of ProductID not just the next ?
data have;
input ID MC $ ServiceDate : yymmdd10. ProductID;
format ServiceDate yymmdd10.;
cards;
1 ABC 2014-01-01 123
1 DEF 2014-02-01 456
1 ABC 2014-03-01 222
1 DEF 2014-04-01 333
1 DEF 2014-05-01 335
1 DEF 2014-05-12 456
1 DEF 2014-05-15 334
;
run;
data map;
input NP_ProductID Preferred_ProductID;
cards;
123 222
123 223
123 224
456 333
456 334
456 335
;
run;
proc sort data=have ;by ID MC ServiceDate ;run;
data want;
if _n_ eq 1 then do;
if 0 then set map;
declare hash h(dataset:'map');
h.definekey('NP_ProductID','Preferred_ProductID');
h.definedone();
end;
set have;
by ID MC;
array x{99999} _temporary_;
if first.MC then do;n=0;count=0;end;
n+1;
x{n}=ProductID;
if last.MC then do;
do i=1 to n-1;
do j=i+1 to n;
NP_ProductID=x{i};
Preferred_ProductID=x{j};
if h.check()=0 then count+1;
end;
end;
output;
end;
keep id MC count;
run;
Xia Keshan
Or another approach i'm trying to work through, is a 3 way lookup:
data NonPrefProducts;
input ID MC $ ServiceDate : yymmdd10. ProductID;
format ServiceDate yymmdd10.;
cards;
1 ABC 2014-01-01 123
1 DEF 2014-02-01 456
1 DEF 2014-05-12 456
;
run;
data PrefProducts;
input ID MC $ ServiceDate : yymmdd10. ProductID;
format ServiceDate yymmdd10.;
cards;
1 ABC 2014-03-01 222
1 DEF 2014-04-01 333
1 DEF 2014-05-01 335
1 DEF 2014-05-15 334
;
run;
data map;
input NP_ProductID Preferred_ProductID;
cards;
123 222
123 223
123 224
456 333
456 334
456 335
;
run;
In the nonPrefProduct table, i'd need to look up whether each ID-MC made a claim for a PrefProduct at a later Service Date AND is valid mapping.
I'm stuck on the coding of this.
This will get you the solution you are looking for without a Hash Table:
data have1;
infile cards;
informat servicedate yymmdd10.;
format servicedate mmddyy10.;
input ID$ MC$ ServiceDate ProductID$;
cards;
1 ABC 2014-01-01 123
1 DEF 2014-02-01 456
1 ABC 2014-03-01 222
1 DEF 2014-04-01 333
;
data have2;
infile cards;
input NP_ProductID$ Preferred_ProductID$;
cards;
123 222
123 223
123 224
456 333
456 334
456 335
;
proc sql;
create table want as
select id,mc,1 as Switch_Cnt
from have1
where productid in
(select preferred_productid
from have2);
Thanks for the reply Mark.
Unfortunately the solution doesn't work in my case.
The mapping table may map multiple non preferred product to a single preferred product, there is a 1: many relationship that does not allow that solution to work.
Can be done with a three-way join:
proc sql;
create table switches as
select a.ID, a.MC, a.productID as from_productID, c.productID as to_productID
from
have as a inner join
map as b on a.productID=b.NP_productID inner join
have as c on a.id=c.id and a.MC=c.MC and
a.serviceDate<c.serviceDate and b.preferred_productID=c.productID
order by ID, MC, from_productID, to_productID;
select * from switches;
quit;
After you get the list of product switches, It is only a matter of counting whatever you want to count.
PG
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.