|
Jack,
1. I am saying that IP will have 4 records (2+2).
2. ETG_FAMILY is a char type and the same length in all data sets
3. That is how the actual out.IP_ output's fragment looks like:
ETG_FAMILY TYPE _FREQ_ SUMTOTFINAL RANK_ALL
*************************************************************
00802 0 0 4 165029176.72 1
01501 0 0 3 90072099.02 4
Actually IP contains 4 products (initially I provided you just with two of them for ex.)
That is how the actual MedCostPPO output's fragment:
ETG_FAMILY Nmember SUMTOT RANKdollar
************************************************
00802 4237 71263541.48 2
01501 9897 92630440.99 1
MedCostInd:
ETG_FAMILY Nmember SUMTOT RANKdollar
*************************************************
00802 2278 16719630.46 1
01501 455 4496278.04 4
MedCostHMO:
ETG_FAMILY Nmember SUMTOT RANKdollar
************************************************
00802 7459 120299222.57 2
01501 17237 144247847.68 1
MedCostBC65:
ETG_FAMILY Nmember SUMTOT RANKdollar
************************************************
00802 2970 23052445.49 1
01501 0 0
Jack Clark <JClark@chpdm.umbc.edu> wrote: v\:* {behavior:url(#default#VML);} o\:* {behavior:url(#default#VML);} w\:* {behavior:url(#default#VML);} .shape {behavior:url(#default#VML);} 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?
Is the ETG_FAMILY char type (and the same length) in both data sets?
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 Yahoo! Mail Beta.
---------------------------------
Yahoo! Messenger with Voice. Make PC-to-Phone Calls to the US (and 30+ countries) for 2¢/min or less.
| 
