* PROGRAM NAME: BUILD_LOOKUP_CMS GENERIC SUB.SAS;

LIBNAME FORM    "&FORMULARY";
LIBNAME INPUT   "&INPUTDIR";
LIBNAME PBP     "&PBPDIR";

OPTIONS MSGLEVEL=I;

DATA PLAN_FORM;
  SET FORM.PLAN_FORMULARY;
  LENGTH CONTPLN $8;
  CONTPLN = CONTRACT_ID||PLAN_ID;
  IF PUT(CONTPLN,$CONTPLNF.) NE 'NO';
RUN;

PROC SORT DATA=PLAN_FORM OUT=PFORMS(KEEP=FORMULARY_ID) NODUPKEY;
  BY FORMULARY_ID;
RUN;

PROC SORT DATA=FORM.FORMULARYDATA (KEEP=FORMULARY_ID RXCUI TIER_LEVEL) OUT=FORMULARY;
  BY FORMULARY_ID RXCUI;
RUN;

DATA FORMULARY_SUBSET (DROP=TIER_LEVEL_OLD);
  MERGE PFORMS (IN=A)
        FORMULARY (IN=B RENAME=TIER_LEVEL=TIER_LEVEL_OLD);
  BY FORMULARY_ID;
  IF A AND B;
  LENGTH TIER_LEVEL 3.;
  TIER_LEVEL=TIER_LEVEL_OLD;
RUN;

DATA PFORMS_MERGE;
  SET PFORMS;
  LENGTH JOIN_ID $1;
  JOIN_ID='1';
RUN;

DATA RXCUI_REFERENCE;
  SET INPUT.RXCUI_REFERENCE;
  LENGTH JOIN_ID $1;  
  JOIN_ID='1';
RUN;

PROC SQL;
  CREATE TABLE RXCUI_MASTER_FILE AS
    (SELECT R.RXCUI, R.DRUG_TYPE, R.AVG_PRICE, F.FORMULARY_ID FROM RXCUI_REFERENCE R,
    PFORMS_MERGE F WHERE R.JOIN_ID=F.JOIN_ID)
    ORDER BY F.FORMULARY_ID, R.RXCUI;
RUN;

DATA FORMULARY_TIERS;
  MERGE RXCUI_MASTER_FILE (IN=A)
        FORMULARY_SUBSET (IN=B);
  BY FORMULARY_ID RXCUI;
  IF A;
  IF A AND NOT B THEN TIER_LEVEL = 9;
RUN;

PROC SQL;
  CREATE TABLE RICPME_SAMPLE_COHORT AS
    SELECT F.FORMULARY_ID, MIN(F.TIER_LEVEL) AS TIER_LEVEL, F.DRUG_TYPE, MIN(F.AVG_PRICE) AS AVG_PRICE,
           B.BASEID, B.SCRIPT_CNT30, B.RX_SCRIPT_ORDER, B.RXCUI
     FROM INPUT.BENE_SCRIPT B, FORMULARY_TIERS F, INPUT.FRF_XWALK X
     WHERE B.RXCUI = X.RXCUI AND X.ALTERNATIVE_RXCUI = F.RXCUI
     GROUP BY F.FORMULARY_ID, B.BASEID, B.RX_SCRIPT_ORDER, B.RXCUI, B.SCRIPT_CNT30
     HAVING MIN(F.AVG_PRICE) = F.AVG_PRICE
     ORDER BY F.FORMULARY_ID, B.BASEID, B.RX_SCRIPT_ORDER, B.RXCUI;
RUN;





