Date:         Tue, 18 Jul 2006 07:09:55 -0700
Reply-To:     Irin later <irinfigvam@YAHOO.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Irin later <irinfigvam@YAHOO.COM>
Subject:      Re: Total is not equal sum of subtotals
Comments: To: Jack Clark <JClark@chpdm.umbc.edu>
In-Reply-To:  <8B5E9B95CC245C46A68BF4495A29A7EEB3C2E9@chpdm-mail.chpdm.umbc.edu>
Content-Type: text/plain; charset=iso-8859-1

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;

Is there additional information you could provide about what is unexpected
in your results?

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?

Thank you in advance!

Irin


---------------------------------
Yahoo! Music Unlimited - Access over 1 million songs.Try it free.



---------------------------------
Do you Yahoo!?
 Everyone is raving about the  all-new Yahoo! Mail Beta.