Hash tables

[DangIT]

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.

[Ksharp]

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]

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]

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]

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]

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]

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 · Accepted solution]

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