215774_en-US

liliconcom

Creating a script to track revisits in medical claims

I am analyzing insurance claims data. The table has a column with dates of the visits. I want to generate another column that shows the recurring visits for each member. The visits will be Continous + ordered based on the sequence of visits - e.g., Visit 1. Visit 2. Visit 3 as per the member. Hence let say I went to a doctor on Jan 1, Jan 10, Jan 30, and March 5, I want the script to generate the recurring sequence of visits as Visits 1, 2, 3, and 4 for every member in the database. i.e, I should be able to use the same sequence for each member based on the sequence of claim dates. 

 

My ultimate goal is the then establish the variability of the cost of care with each recurring visits for members by diagnosis. It's a large data sent hence unable to do it manually. 

 

I tried recurring analysis and that did not get me too far. I am sure there is a simple way to do this... 

 

Can you help me with this analysis? 

 

Re: Creating a script to track revisits in medical claims

Hi,

I'm quite new in scripting, but maybe this helps you. First, you have to order the table by Patient, so same patient rows are together. Then you can use this script:

 

dt = Current Data Table();

Column( "Visit" )[1] = 1;

For( i = 2, i <= N Row( dt ), i++, 

 If( Column( "Patient" )[i] == Column( "Patient" )[i - 1],
  Column( "Visit" )[i] = Column( "Visit" )[i - 1] + 1,
  Column( "Visit" )[i] = 1
 )
);

Re: Creating a script to track revisits in medical claims

There is a previous discussion that covered this:

     New Sequence column creation based on another ID columns

In the discussion, an alternative that did not require the sorting of the data prior to creating the Visit #.  It is also a column format, which means, that running a separate script does not have to be done:

dt = Current Data Table();
theRow = Row();
theCustomer = :Customer ID;
seq = N Rows( dt << get rows where( :Customer ID == theCustomer & Row() < theRow ) ) + 1;

This will run slower that the sorting version, but it might be a better solution. 

Re: Creating a script to track revisits in medical claimsMillion thanks for the suggested script. Could not quite figure out how to
apply script. I am attaching the sample table for your reference as you
generate script.
Thanks a million.