Applying the correct rate.

[tgbcbsm]

Hi All,

I am looking to create a dataset with the following rules.

1) Pull in the correct rate for each pdt_serv_id_num from the RATE_TBL to the CLAIMS_TBL.

2)For every pdt_serv_id_num, If the end_date is other than 29991231, then the rate should be 0 until a record with the end_date 29991231 is found.

3) if a pdt_serv_id_num in the CLAIMS_TBL is not in the RATE_TBL then set the rate to 0.

4) the final dataset should have the same number of records in the CLAIMS_TBL

I have posted the two datasets and what the outcome should be.

DATA RATE_TBL;

INPUT pdt_serv_id_num $11. effective_date Rate1 Rate2 End_Dt; DATALINES;

50242021060 20210901 0.1575 0.1635 29991231

50242021060 20210801 0.15 0.15 29991231

00002144509 20210801 0.18 0.19 29991231

00002144509 20211001 0.18 0.19 20210930

00002144509 20220101 0.1732 0.2467 29991231 ; RUN;

DATA CLAIMS_TBL;

INPUT PCH_DATE  pdt_serv_id_num $11.;

DATALINES;

20211005 50242021060

20210812 50242021060

20210829 50242021060

20211010 50242021060

20211108 50242021060

20210913 50242021060

20211026 50242021060

20210928 50242021060

20211109 78206013801

20211028 78206013801

20210831 00002144509

20210901 00002144509

20210929 00002144509

20211004 00002144509

20211112 00002144509

20220112 00002144509

20220226 00002144509

;

RUN;

DATA WANT;

INPUT PCH_DATE pdt_serv_id_num $11. Rate1 Rate2; DATALINES;

20210831 00002144509 0.18 0.19

20210901 00002144509 0.18 0.19

20211029 00002144509 0 0

20211004 00002144509 0 0

20211112 00002144509 0 0

20220112 00002144509 0.1732 0.2467

20220226 00002144509 0.1732 0.2467

20211005 50242021060 0.1575 0.1635

20210812 50242021060 0.15 0.15

20210829 50242021060 0.15 0.15

20211010 50242021060 0.1575 0.1635

20211108 50242021060 0.1575 0.1635

20210913 50242021060 0.1575 0.1635

20211026 50242021060 0.1575 0.1635

20210928 50242021060 0.1575 0.1635

20211109 78206013801 0 0

20211028 78206013801 0 0

;

RUN;

I hope what I am looking for helps. If not, please let me know.

Thanks,

[GarthHelf]

It seems like you are missing a rule:  what do you want when there are entries for pdt_serv_id_num in the RATE_TBL with overlapping dates?  For example, for 50242021060 there are entries with effective_date of 20210801 and 20210901, both with end_dt of 29991231.  So the entry in CLAIMS_TBL for 50242021060 with PCH_DATE of 20211108 matches both entries in the RATE_TBL.  Which should be used?  From your WANT table, it appears the rule is to use the record in the RATE_TBL with end_dt of 29991231 that has the maximum effective date that is less than PCH_DATE, so in this case it is the RATE_TBL entry with effective_date of 20210901.  Is this correct?