Date:         Tue, 18 Jul 2006 09:08:51 -0400
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject:      Re: Total is not equal sum of subtotals
Content-Type: text/plain; charset=ISO-8859-1

I cannot reproduce your problem.

I ran:

   data PPOcost;
   do ETG_FAMILY = 1 to 3;
      do i = 1 to 5;
         allow_amt = round(10 * ranuni(1) );
         output;
         end;
      end;
   run;

   data INDcost;
   do ETG_FAMILY = 1 to 3;
      do i = 1 to 5;
         allow_amt = round(10 * ranuni(2) );
         output;
         end;
      end;
   run;

  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=outIP sum=sumtotFinal;
  run;

   proc sql;
   select sum(sumtotFinal) from outIP;
   select sum(sumtot) from MedCostPPO;
   select sum(sumtot) from MedCostIND;
   quit;

I got:

        152

         82

         70

82 + 70 = 152.

Perhaps you were comparing results which did not actually correspond as you
believed them to correspond.

When CLASS statements are involved, discrepancies can arise from the way
missing values are handled. However, you are not using CLASS statements.

On Mon, 17 Jul 2006 12:48:29 -0700, Irin later <irinfigvam@YAHOO.COM> wrote:

>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!
>http://www.listserv.uga.edu/cgi-bin/wa?A2=ind0607c&L=sas-l&P=17887&X=4B166225AFA70DBEC1&Y=nospam@howles.com
original message
>  Irin
>
>
>---------------------------------
>Yahoo! Music Unlimited - Access over 1 million songs.Try it free.