I am working with a dataset similar in format to the table below as an example. I need to merge this health insurance dataset with another medical dataset based on ID. That part is simple enough. However, for the insurance dataset, I need to create one row for each patient that describes their health insurance status (0=no insurance; 1=insurance) for the month of each year similar to the table below. If a patient moved to another state they have a different row that identifies their insurance status in that specific state. In my example table below I need one row that shows that patient having continuous coverage even though they lost it in one state and gained it in another. Additionally, if patients have health insurance coverage in two states at the same time point they need to be removed. I am not sure how to proceed with code that can accomplish this task. Any help or suggestions would be appreciated. ID State Match_num Jan-06 Feb-06 Mar-06 Apr-06 May-06 Jun-06 Jul-06 Aug-06 Sep-06 Oct-06 Nov-06 Dec-06 Jan-07 Feb-07 Mar-07 Apr-07 May-07 Jun-07 Jul-07 Aug-07 Sep-07 Oct-07 123456 CA 6 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 123456 WA 6 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0
... View more