Need Help Figuring out the Occurrences in a Data Set

Hi, All

I need help with working a data set…

Here is the situation -

I have a database of CPT codes (healthcare term, a string of 5 numbers), what I need to do is the following:

  1. See how many times each CPT code appears in the data (easy - have already done that)

  2. If there is a secondary CPT code attached to the primary code, I need to figure out how many times this code is the secondary CPT for the primary code.

For example, my primary code is 29888, the secondary code is 29881. However, it can also be 29880. So, I need a way to figure out for each primary code, the frequency of any secondary codes that are billed with it.

i.e., 29888 was billed as the primary code 670 times and 29881 as the secondary code 35 times and 29880 450 times

Does this make sense? If not, I’d be happy to word it differently.

Thank you!

First, it would be helpful to see a data structure.
Second, It is not clear if codes you called secondary could appear by them-self. What’s their grouping criterion?


I attached a sample of the data set in excel to show you the data file.

I think this might clear it up for you. But to answer your question, No, they don’t appear by themselves, if there is a second CPT code, then it would only appear in conjunction with the primary code

Forum.xlsx (823.7 KB)

Thank you!

By secondary code you understand codes in columns following CPT1?

Yes. Exactly.

As it is in that excel doc, the column CPT code 2 is the secondary code.

In essence, for every CPT code 1, how many occurrences of each specific CPT code 2, CPT code 3 and CPT code 4 are there.

Please see attached workflow for second question.
CPT Codes1.knwf (13.9 KB)

1 Like

Awesome! I am very thankful for this! Let me poor over this tonight and see if I have any questions!

Hi, Izaychik63

Thanks again for the workflow,

Can you explain to me how I would take this a step further and test how many times CPT codes 2, 3 and 4 occurred with the primary code?

In essence the same exact thing we just did, but expand it to the other subsequent codes

Thank you!

I’m not clear with the question. The WF counts all the codes 2,3 and 4 to specific code1. As far as your input has other codes they appear on the final table. Prompt me please what’s missed.

Sorry for not being clear.

Hopefully this helps

In healthcare, when a procedure is performed, the work that is performed is assigned a CPT code. For instance, a 29888 is a ACL repair. Sometimes, surgeons will also do other work during the same operation so in addition to billing for the ACl repair they will bill other CPT codes, for example, 29881.

However, not always. Most times only one code is billed - this is the primary code. Other times, 1 secondary code is billed, or several (CPT Codes 3,4 in my file).

What i am trying to figure out is this,

How often are secondary CPT codes billed for a certain primary code and what the frequency of the non-primary codes are that are attached to the primary.

For example -

Primary code 29888 was billed 290 times in the data set with no secondary codes
Primary code 29888 was billed with CPT code 2 (29881) 25 times with no other CPT codes
Primary Code 29888 was billed with CPT code 2 (29811) and CPT code 3 (29882) 15 times.

Does this clear things up at all?

Second line of WF does what you need.
CPT Codes1.knwf (23.9 KB)

1 Like