Need to remove duplicates and merge datasets

[gabe434555]

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

[PaigeMiller · Accepted solution]

proc summary nway data=have;
    class id;
    var <your variable names go here>;
    output out=want sum=;
run;

This gives you one row per patient, with a 1 if they had insurance in any state and a zero if they did not have insurance in any state. If any of the variables in the output data set have a value of two or more, then they had insurance in two or more states.

[gabe434555]

Oh my goodness! Thank you!