MemberID ServiceDate 1 1/17/2017 1 1/2/2017 10 6/27/2017 11 7/31/2017 12 11/1/2017 13 6/14/2017 13 2/4/2016 14 5/30/2017 15 8/11/2017 15 12/11/2016 16 10/16/2017 16 1/14/2017 16 2/17/2016 16 11/25/2015 17 7/8/2017 18 9/14/2017 18 3/3/2017 19 10/12/2017 2 2/14/2017 2 5/25/2016 20 12/19/2017 21 8/16/2017 21 5/25/2015 21 3/11/2014 3 3/17/2017 30 2/18/2018 30 4/16/2016 31 1/21/2018 32 3/6/2018 33 2/7/2018 34 1/3/2018 34 2/15/2016 35 1/13/2018 36 3/30/2018 37 3/16/2018 38 5/1/2016 39 6/15/2016 4 3/21/2017 4 10/16/2016 4 4/12/2013 40 11/27/2016 44 10/31/2017 45 4/18/2017 45 12/11/2015 46 5/25/2016 47 12/23/2015 48 6/12/2017 49 7/14/2017 5 1/11/2017 5 7/4/2016 50 10/12/2017 50 5/25/2017 50 1/11/2016 6 2/8/2017 7 5/26/2017 7 1/15/2017 7 6/25/2016 8 4/16/2017 9 9/21/2017 this is a sample from my data and I want to know what is the best way to assign new variable call year it count this way for each member id if there is only one service date then year=Measurement Year but if the member id has more than service date then Measurement Year- The 12 month period that is being reviewed by the current run of data. This does not have to start on January 1st Prior Year- The 12 month period immediately preceding the measurement year what is the best way to assign measurement year or prior year this is a sample of the result that I aim for MemberID ServiceDate Year 1 1/17/2017 Measurement Year 1 1/2/2017 Measurement Year 10 6/27/2017 Measurement Year 11 7/31/2017 Measurement Year 12 11/1/2017 Measurement Year 13 6/14/2017 Prior Year 13 2/4/2016 Measurement Year 14 5/30/2017 Measurement Year 15 8/11/2017 Measurement Year 15 12/11/2016 Measurement Year 16 10/16/2017 Measurement Year 16 1/14/2017 Measurement Year 16 2/17/2016 Prior Year 16 11/25/2015 Prior Year
... View more
