Solved
Contributor
Posts: 29

# How to determine whether claims have two unique types of codes in them

Using the following example, I was trying to determine which claims had multiple distinct code sets in them, along with their corresponding paid amounts. Not sure if this is possible with SQL only or if multiple data steps and merge are needed.

Thanks as always!

Sorry about that! I initially had those tables within the field, but they wouldn't publish. Here they are:

Code Definitions

BRCA: 81211, 81213

CRC: 81317, 81295

Other: 81479, 81200

Data

MemberID          ClaimID          CPT             CodeSet          PaidAmount

1                         1                  81211          BRCA              \$1000

1                         1                  81317          CRC                \$500

1                         1                  81479          OTHER            \$300

2                         2                  81211          BRCA              \$1000

2                         2                  81213          BRCA              \$400

3                         3                  81211          BRCA              \$1000

3                         3                  81317          CRC                \$400

Multiple = Two unique types of code sets on 1 claim (e.g., BRCA + CRC and/or OTHER on same claim)

Desired Output

Multiple          #Claims          #Members           TotalPaidBRCA      TotalPaidCRC    TotalPaidOther      TotalPaidAll

Yes                    2                     2                            \$2000                   \$900                       \$300                      \$3200

No                      1                    1                            \$1400                     \$0                           \$0                           \$0

Accepted Solutions
Solution
‎05-19-2015 09:40 AM
Super User
Posts: 10,846

## Re: How to determine whether claims have two unique types of codes in them

OK. If I understood you.

```
data  have;
input MemberID          ClaimID          CPT             CodeSet \$         PaidAmount : dollar.;
format      PaidAmount dollar.;
cards;
1                         1                  81211          BRCA              \$1000
1                         1                  81317          CRC                \$500
1                         1                  81479          OTHER            \$300
2                         2                  81211          BRCA              \$1000
2                         2                  81213          BRCA              \$400
3                         3                  81211          BRCA              \$1000
3                         3                  81317          CRC                \$400
;
run;

proc sql;
create table want as
select 'Yes' as multiple,count(distinct ClaimID) as NoClaims , count(distinct MemberID) as  NoMembers ,
sum(case when CodeSet='BRCA' then PaidAmount else 0 end) as TotalPaidBRCA ,
sum(case when CodeSet='CRC' then PaidAmount else 0 end) as TotalPaidCRC ,
sum(case when CodeSet='OTHER' then PaidAmount else 0 end) as TotalPaidOther
from (select * from have group by MemberID,ClaimID having count(distinct CodeSet) gt 1)
union
select 'No' as multiple,count(distinct ClaimID) as NoClaims , count(distinct MemberID) as  NoMembers ,
sum(case when CodeSet='BRCA' then PaidAmount else 0 end) as TotalPaidBRCA ,
sum(case when CodeSet='CRC' then PaidAmount else 0 end) as TotalPaidCRC ,
sum(case when CodeSet='OTHER' then PaidAmount else 0 end) as TotalPaidOther
from (select * from have group by MemberID,ClaimID having count(distinct CodeSet) = 1) ;
quit;

```

Xia Keshan

All Replies
Super User
Posts: 10,846

## Re: How to determine whether claims have two unique types of codes in them

Type your data and output as TEXT . No one would like to do it for you .

Contributor
Posts: 29

## Re: How to determine whether claims have two unique types of codes in them

Sorry about that! I initially had those tables within the field, but they wouldn't publish. Here they are:

Code Definitions

BRCA: 81211, 81213

CRC: 81317, 81295

Other: 81479, 81200

Data

MemberID          ClaimID          CPT             CodeSet          PaidAmount

1                         1                  81211          BRCA              \$1000

1                         1                  81317          CRC                \$500

1                         1                  81479          OTHER            \$300

2                         2                  81211          BRCA              \$1000

2                         2                  81213          BRCA              \$400

3                         3                  81211          BRCA              \$1000

3                         3                  81317          CRC                \$400

Multiple = Two unique types of code sets on 1 claim (e.g., BRCA + CRC and/or OTHER on same claim)

Desired Output

Multiple          #Claims          #Members           TotalPaidBRCA      TotalPaidCRC    TotalPaidOther      TotalPaidAll

Yes                    2                     2                            \$2000                   \$900                       \$300                      \$3200

No                      1                    1                            \$1400                     \$0                           \$0                           \$0

Super User
Posts: 10,846

## Re: How to determine whether claims have two unique types of codes in them

Why would not include ?

NoClaims          NoMembers

3                        3

Contributor
Posts: 29

## Re: How to determine whether claims have two unique types of codes in them

NoClaims & NoMembers refers to the number of each in the Multiple or Non-Multiple groups. I clarified that to make it easier.

There were 2 claims and 2 members who had multiple code sets (BRCA + CRC and/or OTHER) on 1 claim. So that's why it is 2 in the 'Yes' value of the Multiple row. Only 1 claim and 1 member did not have multiple sets - that is why there is only 1  in the 'No' value. Thanks again.

Super User
Posts: 10,846

## Re: How to determine whether claims have two unique types of codes in them

Why would not include ? and don't understand why 2 is Yes , 1 is No ? and how you compute TotalPaidBRCA TotalPaidCRC         TotalPaidOther               TotalPaidAll  ?

NoClaims          NoMembers

3                        3

Contributor
Posts: 29

## Re: How to determine whether claims have two unique types of codes in them

Because the output I'd like is based on identifying which claims have multiple code sets.

There are 3 code sets I defined - BRCA, CRC, and OTHER. I want to identify unique claims with multiple/distinct code sets. If a claim only has BRCA or only has CRC - it is not defined as Multiple.

Based on the data I gave, there is only 2 claims and members with multiple code sets on 1 claim. Member #1 has BRCA (\$1000), CRC (\$500), and Other (\$300). Member #3 has BRCA (\$1000) and CRC (\$400). Therefore there are 2 members with multiple code sets with a total of \$2000 paid for BRCA, \$900 for CRC, and \$300 for OTHER. That is \$3200 total.

On the other side, there is 1 member who did NOT have multiple code sets. They had \$1400 paid for BRCA only.

Hope this is clearer - I realize it's quite convoluted which is why I'm struggling to determine this without creating a myriad of datasets.

Solution
‎05-19-2015 09:40 AM
Super User
Posts: 10,846

## Re: How to determine whether claims have two unique types of codes in them

OK. If I understood you.

```
data  have;
input MemberID          ClaimID          CPT             CodeSet \$         PaidAmount : dollar.;
format      PaidAmount dollar.;
cards;
1                         1                  81211          BRCA              \$1000
1                         1                  81317          CRC                \$500
1                         1                  81479          OTHER            \$300
2                         2                  81211          BRCA              \$1000
2                         2                  81213          BRCA              \$400
3                         3                  81211          BRCA              \$1000
3                         3                  81317          CRC                \$400
;
run;

proc sql;
create table want as
select 'Yes' as multiple,count(distinct ClaimID) as NoClaims , count(distinct MemberID) as  NoMembers ,
sum(case when CodeSet='BRCA' then PaidAmount else 0 end) as TotalPaidBRCA ,
sum(case when CodeSet='CRC' then PaidAmount else 0 end) as TotalPaidCRC ,
sum(case when CodeSet='OTHER' then PaidAmount else 0 end) as TotalPaidOther
from (select * from have group by MemberID,ClaimID having count(distinct CodeSet) gt 1)
union
select 'No' as multiple,count(distinct ClaimID) as NoClaims , count(distinct MemberID) as  NoMembers ,
sum(case when CodeSet='BRCA' then PaidAmount else 0 end) as TotalPaidBRCA ,
sum(case when CodeSet='CRC' then PaidAmount else 0 end) as TotalPaidCRC ,
sum(case when CodeSet='OTHER' then PaidAmount else 0 end) as TotalPaidOther
from (select * from have group by MemberID,ClaimID having count(distinct CodeSet) = 1) ;
quit;

```

Xia Keshan

Contributor
Posts: 29

## Re: How to determine whether claims have two unique types of codes in them

That was perfect - thank you Xia!

I have a follow-up question that is related to this example - feel free to let me know if it's more appropriate to create a new post. Basically it is about how to count the unique members/claims for claims with BRCAMultiple, which I define as "Multiple" claims with BRCA in them (not CRC + OTHER). For example, let's say I had some more data:

Data

MemberID          ClaimID          CPT             CodeSet          PaidAmount

1                         1                  81211          BRCA              \$1000

1                         1                  81317          CRC                \$500

1                         1                  81479          OTHER            \$300

2                         2                  81211          BRCA              \$1000

2                         2                  81213          BRCA              \$400

3                         3                  81211          BRCA              \$1000

3                         3                  81317          CRC                \$400

4                         4                  81479          OTHER            \$300

4                         4                  81317          CRC                \$500

Desired Output

Multiple          #Claims          #Members           TotalPaidBRCA      TotalPaidCRC    TotalPaidOther      TotalPaidAll

Yes                    3                     3                            \$2000                   \$1400                       \$600                      \$4000

BRCAMultiple     2                     2                            \$2000                    \$900                         \$300                     \$3200

No                      1                    1                            \$1400                     \$0                           \$0                           \$0

I tried a union with a where statement = 'BRCA" which I believe gave me the right member/claim totals, but did not give me totalpaid amounts for CRC or OTHER. Using a having statement statement did not give me the right totals for any. Thanks again!

Super User
Posts: 10,846

## Re: How to determine whether claims have two unique types of codes in them

I am a little confused . The output is not like what you said .Yours more like want the groups only contains one BRCA    .

```data  have;
input MemberID          ClaimID          CPT             CodeSet \$         PaidAmount : dollar.;
format      PaidAmount dollar.;
cards;
1                         1                  81211          BRCA              \$1000
1                         1                  81317          CRC                \$500
1                         1                  81479          OTHER            \$300
2                         2                  81211          BRCA              \$1000
2                         2                  81213          BRCA              \$400
3                         3                  81211          BRCA              \$1000
3                         3                  81317          CRC                \$400
4                         4                  81479          OTHER            \$300
4                         4                  81317          CRC                \$500
;
run;

proc sql;
create table want as
select 'Yes' as multiple length=20,count(distinct ClaimID) as NoClaims , count(distinct MemberID) as  NoMembers ,
sum(case when CodeSet='BRCA' then PaidAmount else 0 end) as TotalPaidBRCA ,
sum(case when CodeSet='CRC' then PaidAmount else 0 end) as TotalPaidCRC ,
sum(case when CodeSet='OTHER' then PaidAmount else 0 end) as TotalPaidOther
from (select * from have group by MemberID,ClaimID having count(distinct CodeSet) gt 1)
union
select 'No' as multiple,count(distinct ClaimID) as NoClaims , count(distinct MemberID) as  NoMembers ,
sum(case when CodeSet='BRCA' then PaidAmount else 0 end) as TotalPaidBRCA ,
sum(case when CodeSet='CRC' then PaidAmount else 0 end) as TotalPaidCRC ,
sum(case when CodeSet='OTHER' then PaidAmount else 0 end) as TotalPaidOther
from (select * from have group by MemberID,ClaimID having count(distinct CodeSet) = 1)
union
select 'BRCAMultiple' as multiple,count(distinct ClaimID) as NoClaims , count(distinct MemberID) as  NoMembers ,
sum(case when CodeSet='BRCA' then PaidAmount else 0 end) as TotalPaidBRCA ,
sum(case when CodeSet='CRC' then PaidAmount else 0 end) as TotalPaidCRC ,
sum(case when CodeSet='OTHER' then PaidAmount else 0 end) as TotalPaidOther
from (select * from have group by MemberID,ClaimID having sum(CodeSet='BRCA') =1 )
;
quit;

```

Xia Keshan

Contributor
Posts: 29

## Re: How to determine whether claims have two unique types of codes in them

Xia,

That worked! That was a newer example that was slightly more complicated from my original example. Thank you as always.

If you don't mind a follow-up - how would I go about excluding claims that have a total paid of \$0 within the Code Set. For example, a claim that had BRCA total of \$1000 and \$0 in CRC should not count as multiple. I tried adding in a "where paidamount ne 0," but it is not a full solution because sometimes in the data there are positive \$ and negative \$ amount so it is really the sum(paidamount) = 0 when grouped by member, claim, and Codeset I am looking to exclude.

As an example, Member/Claim 5 should be 'No' value for Multiple because BRCA is \$1000, but CRC is \$0. Member/Claim 6 should be 'No' value also because CRC has a sum total of \$0 for this claim and member.

data  have1;

input MemberID          ClaimID          CPT             CodeSet \$         PaidAmount : dollar.;

format      PaidAmount dollar.;

cards;

1                         1                  81211          BRCA              \$1000

1                         1                  81317          CRC                \$500

1                         1                  81479          OTHER            \$300

2                         2                  81211          BRCA              \$1000

2                         2                  81213          BRCA              \$400

3                         3                  81211          BRCA              \$1000

3                         3                  81317          CRC                \$400

4                         4                  81479          OTHER            \$300

4                         4                  81317          CRC                \$500

5                         5                  81211          BRCA            \$1000

5                         5                  81479          OTHER                \$0

6                         6                  81317          CRC                -\$500

6                         6                  81211          BRCA            \$1000

6                         6                  81317          CRC                \$500

;

run;

The output should be:

 Members Claims BRCA CRC Other BRCA 2 2 \$2,000 \$900 \$300 No 3 3 \$3,400 \$0 \$0 Yes 3 3 \$2,000 \$1,400 \$600
Super User
Posts: 10,846

## Re: How to determine whether claims have two unique types of codes in them

You really ask for too much . I think you owe me some bucks .

```data  have1;
input MemberID          ClaimID          CPT             CodeSet \$         PaidAmount : dollar.;
format      PaidAmount dollar.;
cards;
1                         1                  81211          BRCA              \$1000
1                         1                  81317          CRC                \$500
1                         1                  81479          OTHER            \$300
2                         2                  81211          BRCA              \$1000
2                         2                  81213          BRCA              \$400
3                         3                  81211          BRCA              \$1000
3                         3                  81317          CRC                \$400
4                         4                  81479          OTHER            \$300
4                         4                  81317          CRC                \$500
5                         5                  81211          BRCA            \$1000
5                         5                  81479          OTHER                \$0
6                         6                  81317          CRC                -\$500
6                         6                  81211          BRCA            \$1000
6                         6                  81317          CRC                \$500
;
run;

proc sql;
create table have as
select  MemberID , ClaimID ,CPT , CodeSet,     sum(PaidAmount) as PaidAmount
from have1
group by  MemberID , ClaimID ,CPT , CodeSet;

create table want as
select 'Yes' as multiple length=20,count(distinct ClaimID) as NoClaims , count(distinct MemberID) as  NoMembers ,
sum(case when CodeSet='BRCA' then PaidAmount else 0 end) as TotalPaidBRCA ,
sum(case when CodeSet='CRC' then PaidAmount else 0 end) as TotalPaidCRC ,
sum(case when CodeSet='OTHER' then PaidAmount else 0 end) as TotalPaidOther
from (select * from have where PaidAmount ne 0  group by MemberID,ClaimID having count(distinct CodeSet) gt 1)
union
select 'No' as multiple,count(distinct ClaimID) as NoClaims , count(distinct MemberID) as  NoMembers ,
sum(case when CodeSet='BRCA' then PaidAmount else 0 end) as TotalPaidBRCA ,
sum(case when CodeSet='CRC' then PaidAmount else 0 end) as TotalPaidCRC ,
sum(case when CodeSet='OTHER' then PaidAmount else 0 end) as TotalPaidOther
from (select * from have where PaidAmount ne 0 group by MemberID,ClaimID  having count(distinct CodeSet) = 1)
union
select 'BRCAMultiple' as multiple,count(distinct ClaimID) as NoClaims , count(distinct MemberID) as  NoMembers ,
sum(case when CodeSet='BRCA' then PaidAmount else 0 end) as TotalPaidBRCA ,
sum(case when CodeSet='CRC' then PaidAmount else 0 end) as TotalPaidCRC ,
sum(case when CodeSet='OTHER' then PaidAmount else 0 end) as TotalPaidOther
from (select * from have where PaidAmount ne 0 group by MemberID,ClaimID having sum(CodeSet='BRCA') =1 and count(distinct CodeSet) gt 1)
;
quit;

```

Xia Keshan

🔒 This topic is solved and locked.