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 |
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.