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
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
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;
⏰
Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.
Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.