BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DangIT
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

PG

View solution in original post

7 REPLIES 7
Ksharp
Super User

Code: Program

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;

DangIT
Fluorite | Level 6

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;



Ksharp
Super User

Do you want check all the combination of ProductID  not just the next ?

Code: Program

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

DangIT
Fluorite | Level 6

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.

Steelers_In_DC
Barite | Level 11

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);

DangIT
Fluorite | Level 6

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.

PGStats
Opal | Level 21

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

PG

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!

What is Bayesian Analysis?

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.

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
  • 7 replies
  • 1205 views
  • 3 likes
  • 4 in conversation