Date: Tue, 18 Jul 2006 10:28:15 -0400 Jack Clark "SAS(r) Discussion" Jack Clark Re: Total is not equal sum of subtotals To: Irin later

Irin,

Three questions...

1. When you say "# of records from MedCostPPO & MedCostIND is equal to # of records from IP ", does this mean if MEDCOSTPPO has 2 records and MEDCOSTIND has 2 records, that IP will have 2 records? Or are you saying that IP will have 4 records?

2. Is the ETG_FAMILY char type (and the same length) in both data sets?

3. Can you show the actual output from the example you sent below?

Thanks.

Jack

_____

From: Irin later [mailto:irinfigvam@yahoo.com] Sent: Tuesday, July 18, 2006 10:10 AM To: Jack Clark; 'Irin later' Cc: SAS-L@LISTSERV.UGA.EDU Subject: RE: Total is not equal sum of subtotals

Jack, I summarize dollars per ETG_FAMILY and the output per ERG_FAMILY per line is different.

The output of MedCostPPO looks like:

ETG_FAMILY SUMTOT

01501 92,631,034

00802 71,296,863

The output of MedCostIND

ETG_FAMILY SUMTOT

01501 4,496,290

00802 16,736,682

THEREFORE the output from IP SHOULD BE :

ETG_FAMILY SUMTOT

01501 97,127,324

00802 88,033,545

However the output is different while the number of records in the log is the same. # of records from MedCostPPO & MedCostIND is equal to # of records from IP. Sum per record per ETG_FAMILY differs!

What I am doing wrong ?

Thank You!

Irin

Jack Clark <JClark@chpdm.umbc.edu> wrote:

Irin,

I didn't see any problem with your code. I got the expected sums running the following code...

data ppocost; infile cards; input@01 etg_family \$char2. @04 allow_amt 2. ; cards; AA 10 AA 10 BB 20 BB 20 CC 30 CC 30 ; run;

Proc SQL ; Create Table MedCostPPO As Select ETG_FAMILY,sum(allow_amt) as sumtot From PPOcost Group By ETG_FAMILY; Quit;

proc print data = medcostppo; run;

data indcost; infile cards; input@01 etg_family \$char2. @04 allow_amt 2. ; cards; AA 20 AA 20 BB 30 BB 30 CC 40 CC 40 ; run;

Proc SQL ; Create Table MedCostIND As Select ETG_FAMILY, sum(allow_amt) as sumtot From INDcost Group By ETG_FAMILY; Quit;

data IP; set MedCostPPO MedCostIND; run;

PROC SORT data=IP; BY ETG_FAMILY; run;

proc means data =IP noprint; by ETG_FAMILY; var sumtot; output out=IP_out sum=sumtotFinal; run;

Proc Rank data=IP_out Out=IP_final ties=low descending ; var sumtotFinal; Ranks rank_ALL; Run;

Jack

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Irin later Sent: Monday, July 17, 2006 3:48 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Total is not equal sum of subtotals

I run the following code: Proc SQL ; Create Table MedCostPPO As Select ETG_FAMILY,sum(allow_amt) as sumtot From PPOcost Group By ETG_FAMILY; Quit;

Proc SQL ; Create Table MedCostIND As Select ETG_FAMILY, sum(allow_amt) as sumtot From INDcost Group By ETG_FAMILY; Quit;

data IP; set MedCostPPO MedCostIND; run;

PROC SORT data=IP; BY ETG_FAMILY; run;

proc means data =IP noprint; by ETG_FAMILY; var sumtot; output out=OUT.IP sum=sumtotFinal; run;

Proc Rank data=OUT.IP Out=OUT.IP_ ties=low descending ; var sumtotFinal; Ranks rank_ALL; Run;

.... and the output is totally unexpected.

I have beaten my head trying to resolve the problem. The thing is that after I created dataset IP by concatenating two data sets MedCostPPO MedCostIND I tried to sum their sumtot into sumtotFinal through PROC MEANS and then calculate a rank.

However the total sumtotFinal is NOT equal the sum of subtotals (sumtot from MedCostPPO plus sumtot from MedCostIND). It differs dramatically.

What I am doing wrong?