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,
... View more