I have a dataset similar to the one below. I have shortened the example, but I have a column for every month and year from 2006-2013. Each column represents whether a person has health insurance (1=Yes; 0=No) or not during that specific month and year. I have another date variable indicating when a person was diagnosed with condition X. I need to create a new indicator variable to identify people into 1 of 3 categories: 1) those with health insurance within the 12 months prior to their diagnosis; 2) those who only got health insurance in the month of or 12 months after their diagnosis; 3) those without health insurance at all. I know there is likely a way to more efficiently complete this rather than a ton of IF/THEN statements, but I am unsure of how to best proceed.   Any help would be greatly appreciated   ID Jan_2006 Jan_2007 Jan_2008 Feb_2006 Feb_2007 Feb_2008 Mar_2006 Mar_2007 Mar_2008 Apr_2006 Apr_2007 Apr_2008 Diagnosed 589765 1 1 1 1 1 0 1 0 0 1 0 0 02/01/2007 235698 0 0 1 0 0 1 0 0 1 0 0 1 03/01/2006 784956 0 0 0 0 0 0 0 0 0 0 0 0 01/01/2008 ... View more

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