topic Re: Using Proc Report to calculate the unique counts at sub total and total levels in SAS Procedures

Using Proc Report to calculate the unique counts at sub total and total levels

Soha — Tue, 27 Sep 2011 19:00:17 GMT

Hi All,

I am a newbie to the PRCO REPORT. I have the following requirement. Can someone please help me with it? Thanks in advance

My Data:

MCO Region	Member ID	Member DOB	Provider NPI	Claim ICN	First DOS	Last DOS	Paid Amount
02	0000084251	09/23/2004	2365785210	642351	08/05/2011	08/05/2011	$370.00
02	0003462381	10/03/1981	6325458740	642750	08/10/2011	08/10/2011	$150.00
02	0003650931	05/01/1985	4569851210	642810	08/18/2011	08/18/2011	$227.00
02	0003650931	05/01/1985	4569851210	642820	08/19/2011	08/19/2011	$150.00
04	0000048001	09/17/2002	4565785210	642355	08/09/2011	08/09/2011	$255.00
04	0000048321	09/18/1951	6375458740	642780	08/12/2011	08/12/2011	$188.00
04	0000083701	09/10/2004	4563651210	642845	08/24/2011	08/24/2011	$110.00

The required report format:

MCO Region	Member ID	Member DOB	Provider NPI	Claim ICN	First DOS	Last DOS	Paid Amount
02	0000084251	09/23/2004	2365785210	642351	08/05/2011	08/05/2011	$370.00
02	0003462381	10/03/1981	6325458740	642750	08/10/2011	08/10/2011	$150.00
02	0003650931	05/01/1985	4569851210	642810	08/18/2011	08/18/2011	$227.00
02	0003650931	05/01/1985	4569851210	642820	08/19/2011	08/19/2011	$150.00

Sub Total	3			4			$897.00

04	0000048001	09/17/2002	4565785210	642355	08/09/2011	08/09/2011	$255.00
04	0000048321	09/18/1951	6375458740	642780	08/12/2011	08/12/2011	$188.00
04	0000083701	09/10/2004	4563651210	642845	08/24/2011	08/24/2011	$110.00

Sub Total	3			3			$553.00

Total	6			7			$1,450.00

I specifically want to know how can I get the count of the unique number of MemberIDs in the subtotal and total rows.

Thanks

Sohail Mohammad

Re: Using Proc Report to calculate the unique counts at sub total and total levels

Ksharp — Thu, 29 Sep 2011 09:44:48 GMT

How about:

data temp;
input (MCORegion     MemberID     MemberDOB     ProviderNPI     ClaimICN     FirstDOS     LastDOS) (:$20.)     PaidAmount : dollar8.;
format PaidAmount dollar8.;
cards;
02     0000084251     09/23/2004     2365785210     642351     08/05/2011     08/05/2011     $370.00
02     0003462381     10/03/1981     6325458740     642750     08/10/2011     08/10/2011     $150.00
02     0003650931     05/01/1985     4569851210     642810     08/18/2011     08/18/2011     $227.00
02     0003650931     05/01/1985     4569851210     642820     08/19/2011     08/19/2011     $150.00
04     0000048001     09/17/2002     4565785210     642355     08/09/2011     08/09/2011     $255.00
04     0000048321     09/18/1951     6375458740     642780     08/12/2011     08/12/2011     $188.00
04     0000083701     09/10/2004     4563651210     642845     08/24/2011     08/24/2011     $110.00
;
run;
proc report data=temp nowd out=test;
column  MCORegion     MemberID     MemberDOB     ProviderNPI     ClaimICN     FirstDOS     LastDOS  PaidAmount;
define  MCORegion/group;
compute before MCORegion;
 count=0; _count=0;  s=' ';
endcomp;
compute PaidAmount ;
a+1;if a=1 then do; length s ss $ 200; s=' ';ss=' ';count=0;_count=0;end;
if missing(_break_) then do;
 if not find(s,strip(MemberID)) then do;
   count+1; s=catx(' ',s,MemberID);  
  end;

if not find(ss,strip(ClaimICN)) then do;
   _count+1; ss=catx(' ',ss,ClaimICN);  
  end;
end;
endcomp;
compute after MCORegion;
sum+count; _sum+_count;
line ' ';
line @10 'Sub Total:'  @24 count best8. @40 _count best8. @80 paidamount.sum dollar10.2;
line ' ';
endcomp;
compute after;
line ' ';
line @10 'Total:'  @24 sum best8. @40 _sum best8. @80 paidamount.sum dollar10.2;
endcomp;
run;

Ksharp

Re: Using Proc Report to calculate the unique counts at sub total and total levels

Cynthia_sas — Thu, 29 Sep 2011 20:13:39 GMT

Hi:

There are 2 other solutions possible (well, there are probably more, but I have two alternate solutions to offer). My solutions do NOT use the LINE statement -- because the LINE statement can only be used for accurate column pointer positioning in the LISTING destination and you might not like the WAY that LINE statement output looks in other ODS destinations.

My interpretation of your original question was that you wanted to count unique Member IDs for each Region -- not unique Claim ICN values. So my logic is a bit different from the other posted solution.

The attached screen shots represent the output from REPORT #1 and REPORT #2 of the program below. Because you wanted MCO Region and Member ID to appear on every report row, I made 2 character variables that would be able to display those values on every row -- otherwise, the default REPORT behavior would be to suppress the repetitious display of ORDERed values. Then #1 uses all PROC REPORT logic to compute a column called UNIQMEM -- which will be set to 1 or 0 based on the Member ID. Then the summary value for UNIQMEM is put into the CHAR_MEM column -- after you understand how UNIQMEM is being used, you can use the NOPRINT option to hide it. I only show it on the report for purposes of having you understand how UNIQMEM can be computed inside the REPORT COMPUTE block.

Then in step 2, I use a slightly different approach -- I use BY group processing to make a copy of the dataset, with a new variable called UNIQCNT. Essentially, UNIQCNT is going to be handled the same way as UNIQMEM in program #1 -- it's just calculated outside the REPORT step -- which is something you might want to consider if you have multiple BY variables to test or count for. Again, after you understand how UNIQCNT is being created and used, you can use NOPRINT to not display it on the report.

The result of the 2 different steps is shown in the attached screen shots.

cynthia

*** The program;

data testdata;
length char_mco $10 char_memid $10;
format MCORegion z2. MemberID z10.
MemberDOB FirstDOS LastDOS mmddyy10.
ProviderNPI 10.0 PaidAmount dollar12.2;
infile datalines dlm=' ' missover;

input MCORegion MemberID MemberDOB : mmddyy. ProviderNPI ClaimICN

FirstDOS : mmddyy. LastDOS : mmddyy. PaidAmount : comma.;

** create char_mco and char_memid because want to see;
** each region and id repeated on every report row;
** normal behavior of proc report would be to suppress;
** duplicate values of these vars from appearing on report rows;
** will use numeric vars for ordering and char version for;
** display on each report row;
char_mco = right(put(mcoregion,z2.));
char_memid = right(put(memberid,z10.));
return;
datalines;
02 0000084251 09/23/2004 2365785210 642351 08/05/2011 08/05/2011 $370.00
02 0003462381 10/03/1981 6325458740 642750 08/10/2011 08/10/2011 $150.00
02 0003650931 05/01/1985 4569851210 642810 08/18/2011 08/18/2011 $227.00
02 0003650931 05/01/1985 4569851210 642820 08/19/2011 08/19/2011 $150.00
04 0000048001 09/17/2002 4565785210 642355 08/09/2011 08/09/2011 $255.00
04 0000048321 09/18/1951 6375458740 642780 08/12/2011 08/12/2011 $188.00
04 0000083701 09/10/2004 4563651210 642845 08/24/2011 08/24/2011 $110.00
;
run;

** for report #2, use BY group processing to create;
** the UNIQCNT variable, which will be 0 or 1;

proc sort data=testdata;
by mcoregion memberid;
run;

data finaltest;
set testdata; by mcoregion memberid;
uniqcnt = 0;
if first.memberid then uniqcnt = 1;
run;

title; footnote;

ods listing close;
ods html file='c:\temp\mcoregion.html' style=sasweb;
proc report data=testdata nowd;
title '1) Using WORK.TESTDATA, where UNIQMEM Calculated in COMPUTE Block';
column MCORegion MemberID char_mco char_memid uniqmem MemberDOB
         ProviderNPI ClaimICN FirstDOS
         LastDOS PaidAmount ;
define mcoregion / order f=z2. noprint;
define memberid / order noprint;
define char_mco / display 'MCO Region' f=$10.
         style(column)={just=r};
define char_memid / display 'Member ID' f=$10.
         style(column)={just=r};
define uniqmem / computed /* noprint */ 'calc/uniqmem/in/REPORT';
define memberdob / display 'Member DOB';
define providernpi / display f=11. 'Provider NPI';
define claimicn / display 'Claim ICN';
define firstdos / display 'First DOS';
define lastdos / display 'Last DOS';
define paidamount / sum 'Paid Amount';
compute before mcoregion;
    ** reset memtot to 0 for every region;
    length memtot 8;
    memtot = 0;
endcomp;
compute uniqmem;
    ** calculate uniqueness;
    ** if memberid = missing or . then this id is a duplicate;
    length uniqmem memtot 8;
    if _break_ = ' ' then do;
      if memberid = . then uniqmem = 0;
      else if memberid ne . then uniqmem = 1;
      memtot + uniqmem;
      gtot + uniqmem;
    end;
    if upcase(_break_) = 'MCOREGION' then uniqmem = memtot;
    else if _break_ = '_RBREAK_' then uniqmem = gtot;
endcomp;
break after mcoregion / summarize;
rbreak after / summarize;
compute after mcoregion;
    char_mco = 'SubTotal';
    char_memid = put(uniqmem,comma10.);
    line ' ';
endcomp;
compute after;
    char_mco = 'Total';
    char_memid = put(uniqmem,comma10.);
endcomp;
run;


proc report data=finaltest nowd;
title '2) Using WORK.FINALTEST, where UNIQCNT Calculated in DATA Step';
column MCORegion MemberID char_mco char_memid uniqcnt MemberDOB
         ProviderNPI ClaimICN FirstDOS
         LastDOS PaidAmount ;
define mcoregion / order f=z2. noprint;
define memberid / order noprint;
define char_mco / display 'MCO Region' f=$10.
         style(column)={just=r};
define char_memid / display 'Member ID' f=$10.
         style(column)={just=r};
define uniqcnt / sum 'calc/uniqcnt/in/DATA step';
define memberdob / display 'Member DOB';
define providernpi / display f=11. 'Provider NPI';
define claimicn / display 'Claim ICN';
define firstdos / display 'First DOS';
define lastdos / display 'Last DOS';
define paidamount / sum 'Paid Amount';
break after mcoregion / summarize;
rbreak after / summarize;
compute after mcoregion;
    char_mco = 'SubTotal';
    char_memid = put(uniqcnt.sum,comma10.);
    line ' ';
endcomp;
compute after;
    char_mco = 'Total';
    char_memid = put(uniqcnt.sum,comma10.);
endcomp;
run;
ods _all_ close;
title;