|
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;
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
<http://us.rd.yahoo.com/evt=42297/*http:/advision.webevents.yahoo.com/handra
isers> Yahoo! Mail Beta.
| 
