Sum Claims by Member ID

Reply
Occasional Contributor
Posts: 9

Sum Claims by Member ID

I need to find Total Claims per member id >=  X dollars. All info is located in the same claims table.

MBR_ ID     CLM_PD
1234                    0

1245                $92

1246            $850.34

1246            $1643.27

1246                 $ 9.53

1247               $50

Thanks in advance.

Super User
Super User
Posts: 7,970

Re: Sum Claims by Member ID

Hi,

Something like (assuming dollars are numeric):

proc sql;

     create table WANT as

     select     distinct

                    MBR_ID,

                    SUM(CLM_PD) as TOTAL_CLAIMS

     from        HAVE

     having     TOTAL_CLAIMS > x dollars;

quit;

Occasional Contributor
Posts: 9

Re: Sum Claims by Member ID

Will this give me one total for member 1246----notice that 1246 has 3 different claims?

Thanks.

Super User
Super User
Posts: 7,970

Re: Sum Claims by Member ID

Oops, yes.  Forgot the group by:

proc sql;

     create table WANT as

     select     distinct

                    MBR_ID,

                    SUM(CLM_PD) as TOTAL_CLAIMS

     from        HAVE

     group by MBR_ID

     having     TOTAL_CLAIMS > x dollars;

quit;

This should give you one record per MBR_ID which has the sum of any values within that group of MBR_ID.  Note I have trouble with the having sometimes, so you might want to put the select into a sub-query and then where that:

proc sql;

     create table WANT as

     select   *

     from     (

     select     distinct

                    MBR_ID,

                    SUM(CLM_PD) as TOTAL_CLAIMS

     from        HAVE

     group by MBR_ID)

     where    TOTAL_CLAIMS > x dollars;

quit;

Occasional Contributor
Posts: 9

Re: Sum Claims by Member ID

Ok RW9. That worked, thanks!

Want to add one thing----need to add a field (Ins_CD) that resides on seperate member table (the member id is named differently on the 2 tables MBR_ID and MBR_S_ID)

Super User
Super User
Posts: 7,970

Re: Sum Claims by Member ID

Then your looking a joins, if its a straight-forward 1-1 to then:

proc sql;

     create table WANT as

     select     distinct

                    MBR_ID,

                    SUM(CLM_PD) as TOTAL_CLAIMS

     from        (select     A.*,

                                   B.INS_CD

                      from     HAVE A

                      left join  OTHER_TABLE B

                      on           A.MBR_ID=B.MBR_S_ID)

     group by MBR_ID

     having     TOTAL_CLAIMS > x dollars;

quit;

Super User
Posts: 10,035

Re: Sum Claims by Member ID

Assuming sum clm if it gt 10 dollar.

data have;
input MBR_ID     CLM_PD     : dollar12.;
cards;
1234                    0
1245                $92
1246            $850.34
1246            $1643.27
1246                 $ 9.53
1247               $50
;
run;
data want; 
set have;
by MBR_ID;
if first.MBR_ID then sum=0;
 if  CLM_PD gt 10 then sum+CLM_PD;
 if last.MBR_ID then output;
 drop CLM_PD ;
run;


Xia Keshan

Trusted Advisor
Posts: 1,228

Re: Sum Claims by Member ID

proc sql;

select mbr_id,count(*) as cnt_gt_10,sum(clm_pd) as total_claims

from have

where clm_pd>10

group by mbr_id;

quit;

Ask a Question
Discussion stats
  • 7 replies
  • 283 views
  • 0 likes
  • 4 in conversation