When I use multiple SUM's for each sort level of a report, I get the wrong result unless I specifically reference each field in the compute at that level.

To illustrate, run the following code, setting &bug = 0 and then &bug = 1. This took all day to to find and surprised me greatly. I'm worried I have this same issue (when &bug = 1) in other reports I've written.

I really need to understand what is going on here. I've appreciate any clarification.

The whole reason for doing it is the user gets to pick what to sort on for each level ... so I use this logic to sort my results. I do this ahead of time, before the final output, because I then process the hold file further to display the top 10 and sum the rest in an 11th record called Other.

-* Descending sort on (RCOST - DCOST) at each level by country, car, bodytype, MPG and model
-* When set &bug = 1, the compute DIFFCOST at each level does not give expected result ... I need to reference DCOST at each level.
-SET &bug = 0;
TABLE FILE CAR
-*L1: country
SUM
RCOST AS 'L1RCOST'
-IF &bug EQ 1 THEN GOTO L1BUG;
DCOST AS 'L1DCOST'
-L1BUG
COMPUTE L1DIFFCOST/D7 = RCOST - DCOST;
DCOST AS 'L1DCOST'
BY COUNTRY
-*L2: car
SUM
RCOST AS 'L2RCOST'
-IF &bug EQ 1 THEN GOTO L2BUG;
DCOST AS 'L2DCOST'
-L2BUG
COMPUTE L2DIFFCOST/D7 = RCOST - DCOST;
BY COUNTRY
BY CAR
-*L3: bodytype
SUM
RCOST AS 'L3RCOST'
-IF &bug EQ 1 THEN GOTO L3BUG;
DCOST AS 'L3DCOST'
-L3BUG
COMPUTE L3DIFFCOST/D7 = RCOST - DCOST;
BY COUNTRY
BY CAR
BY BODYTYPE
-*L4: MPG
SUM
RCOST AS 'L4RCOST'
-IF &bug EQ 1 THEN GOTO L4BUG;
DCOST AS 'L4DCOST'
-L4BUG
COMPUTE L4DIFFCOST/D7 = RCOST - DCOST;
BY COUNTRY
BY CAR
BY BODYTYPE
BY MPG
PRINT
MODEL
RCOST AS 'L5RCOST'
DCOST AS 'L5DCOST'
COMPUTE L5DIFFCOST/D7 = RCOST - DCOST;
BY COUNTRY
BY CAR
BY BODYTYPE
BY MPG
BY MODEL
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS HOLD1
END

TABLE FILE HOLD1
PRINT *
BY L1DIFFCOST
BY L2DIFFCOST
BY L3DIFFCOST
BY L4DIFFCOST
BY L5DIFFCOST
END

This message has been edited. Last edited by: JudyM,

Hi,

COMPUTE will be evaluated after filter, sort and aggregation. So it won't give you expected result.

If the expression is difference of 2 fields, why don't you use Virtual Fields with DEFINE.

"AS" names are applied at output time, not for processing within the internal matrix. So when you remove the explicit DCOST columns from the code, this column is placed in the internal matrix only once - for the first COMPUTE. For subsequent verb levels, references to DCOST use the column that is already in the internal matrix from the first level COMPUTE (which is a summation BY COUNTRY). For each level, the sum of DCOST by country is subtracted from RCOST at that level, which results in the unexpected negative numbers. When you reference DCOST explicitly at each verb level, it is inserted and calculated at that level within the internal matrix - thereby producing the correct results. You can avoid this confusion by using DEFINEs, as Ram suggests, because DEFINEd columns do get placed in the internal matrix.

-SET &bug = 1;
DEFINE FILE CAR
 L1RCOST/D7 = RCOST ;
 L2RCOST/D7 = RCOST ;
 L3RCOST/D7 = RCOST ;
 L4RCOST/D7 = RCOST ;
 L5RCOST/D7 = RCOST ;
 L1DCOST/D7 = DCOST ;
 L2DCOST/D7 = DCOST ;
 L3DCOST/D7 = DCOST ;
 L4DCOST/D7 = DCOST ;
 L5DCOST/D7 = DCOST ;
END
-*
TABLE FILE CAR
-*L1: country
 SUM
  L1RCOST
-IF &bug EQ 1 THEN GOTO L1BUG;
  L1DCOST
-L1BUG
 COMPUTE L1DIFFCOST/D7 = L1RCOST - L1DCOST;
 BY COUNTRY
-*L2: car
 SUM
  L2RCOST
-IF &bug EQ 1 THEN GOTO L2BUG;
  L2DCOST
-L2BUG
 COMPUTE L2DIFFCOST/D7 = L2RCOST - L2DCOST;
 BY COUNTRY
 BY CAR
-*L3: bodytype
 SUM
  L3RCOST
-IF &bug EQ 1 THEN GOTO L3BUG;
  L3DCOST
-L3BUG
 COMPUTE L3DIFFCOST/D7 = L3RCOST - L3DCOST;
 BY COUNTRY
 BY CAR
 BY BODYTYPE
-*L4: MPG
 SUM
  L4RCOST
-IF &bug EQ 1 THEN GOTO L4BUG;
  L4DCOST
-L4BUG
 COMPUTE L4DIFFCOST/D7 = L4RCOST - L4DCOST;
 BY COUNTRY
 BY CAR
 BY BODYTYPE
 BY MPG
-*L5: MODEL
 PRINT
  MODEL
  L5RCOST
  L5DCOST
 COMPUTE L5DIFFCOST/D7 = L5RCOST - L5DCOST;
 BY COUNTRY
 BY CAR
 BY BODYTYPE
 BY MPG
 BY MODEL
 ON TABLE SET ASNAMES ON
 ON TABLE HOLD AS HOLD1
END
-*
TABLE FILE HOLD1
 PRINT *
 BY L1DIFFCOST
 BY L2DIFFCOST
 BY L3DIFFCOST
 BY L4DIFFCOST
 BY L5DIFFCOST
END

Another approach is to explicitly reference DCOST at each level, but use NOPRINT to suppress its display when necessary. This will eliminate the need for the DEFINEs.

-*-SET &NOPRINT = '';
-SET &NOPRINT = 'NOPRINT';
-*
TABLE FILE CAR
-*L1: country
 SUM
  RCOST AS 'L1RCOST'
  DCOST AS 'L1DCOST' &NOPRINT
 COMPUTE L1DIFFCOST/D7 = RCOST - DCOST;
 BY COUNTRY
-*L2: car
 SUM
  RCOST AS 'L2RCOST'
  DCOST AS 'L2DCOST' &NOPRINT
 COMPUTE L2DIFFCOST/D7 = RCOST - DCOST;
 BY COUNTRY
 BY CAR
-*L3: bodytype
 SUM
  RCOST AS 'L3RCOST'
  DCOST AS 'L3DCOST' &NOPRINT
 COMPUTE L3DIFFCOST/D7 = RCOST - DCOST;
 BY COUNTRY
 BY CAR
 BY BODYTYPE
-*L4: MPG
 SUM
  RCOST AS 'L4RCOST'
  DCOST AS 'L4DCOST' &NOPRINT
 COMPUTE L4DIFFCOST/D7 = RCOST - DCOST;
 BY COUNTRY
 BY CAR
 BY BODYTYPE
 BY MPG
-*L5: MODEL
 PRINT
  MODEL
  RCOST AS 'L5RCOST'
  DCOST AS 'L5DCOST'
 COMPUTE L5DIFFCOST/D7 = RCOST - DCOST;
 BY COUNTRY
 BY CAR
 BY BODYTYPE
 BY MPG
 BY MODEL
 ON TABLE SET ASNAMES ON
 ON TABLE SET HOLDLIST PRINTONLY
 ON TABLE HOLD AS HOLD1
END
-*
TABLE FILE HOLD1
 PRINT *
 BY L1DIFFCOST
 BY L2DIFFCOST
 BY L3DIFFCOST
 BY L4DIFFCOST
 BY L5DIFFCOST
END

OK, I see why COMPUTE does not work when &bug = 1.

My car example is an oversimplification ... it is usually a percentage, so that is why I am not doing it in the define.

Now why does the COMPUTE work when &bug = 0? I'm thinking that is my workaround, but I don't really understand why it works, so I'm not sure I can count on it.

Thank you for the replies. What was said is making sense to me now. We can close this now. How do I show a thread is RESOLVED or CLOSED?

After you logon to the site, you should be able to edit your original message, including editing the subject. Simply place a [CLOSED] or [RESOLVED] at the front of the subject line.