I'd like to have two columns in my report: one that sums sales for ENGLAND and one that sums sales for JAPAN. How would I do that?

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

Read up on the ACROSS statement. Easiest way to turn rows into columns

Is there a way to compute a difference for two of the across columns?

Yes there is.

The exact code depends on what your reuqest looks like, since the method uses relative column adressing.

For instance this request:

TABLE FILE CAR
SUM RCOST
BY SEATS
ACROSS COUNTRY
COMPUTE DIFF/D12 = C2-C1;
IF COUNTRY EQ 'ITALY' OR 'ENGLAND'
END

It gives you the difference of the two columns.

That works great ( I just adjusted the column numbers for my report).

The output I get is:

COUNTRY
ENGLAND ITALY DIFF
SEATS
-------------------------------------
2 13,978 45,140 31,162
4 17,850 5,925 -11,925
5 13,491 . -13,491

I will be shutting off COUNTRY by doing an AS '' for the ACROSS. Is there any way to do the following:
1) Rename the ENGLAND and ITALY columns?
2) Get the ENGLAND and ITALY titles to be on the same line as SEATS?

Also, another thing I'd like to be able to do...

After the dif column for the sum of RCOST by SEATS Across countries, I'd like SUM RCOST for the same countries across BY CAR within each BY SEAT.

For example, for the 2 SEATS, I'd like to then see which cars have 2 SEATS in England and Italy and the SUM of their RCOST - is there any way to do that in the same report?

For the first, use define rather than across:

DEFINE FILE CAR
  ENGLAND/D5=RCOST * (COUNTRY IS 'ENGLAND');
  JAPAN  /D5=RCOST * (COUNTRY IS 'JAPAN  ');
  DIFF   /D5=ENGLAND-JAPAN;
END
TABLE FILE CAR
SUM RCOST ENGLAND JAPAN DIFF
BY SEATS
ON TABLE SUMMARIZE
END
  

For the second, I only got so far:

Using multiple verbs you can get a correct total RCOST (over all countries), while limiting the scope of detail rows produced by the second verb to selected countries by means of IF/WHERE TOTAL:

DEFINE FILE CAR
  ENGLAND/D5=RCOST * (COUNTRY IS 'ENGLAND');
  JAPAN  /D5=RCOST * (COUNTRY IS 'JAPAN  ');
END
TABLE FILE CAR
SUM RCOST ENGLAND JAPAN COMPUTE DIFF/D5=ENGLAND-JAPAN;
  BY SEATS
SUM RCOST
  BY SEATS
    BY COUNTRY
    BY CAR
  IF TOTAL COUNTRY EQ ENGLAND OR JAPAN
-*ON COUNTRY SUMMARIZE
END
  

But if you add subtotals, you get phantom subtotal lines for the other countries.

And if you change BY CAR to ACROSS CAR, even w/o totaling other things break -- IF TOTAL and ACROSS do not seem to mix well.

This message has been edited. Last edited by: j.gross,

Perhaps I wasn't clear with what I was trying to do but with your first suggestion I was easily able to do it as follows:

DEFINE FILE CAR
ENGLAND/D5=RCOST * (COUNTRY IS 'ENGLAND');
JAPAN /D5=RCOST * (COUNTRY IS 'JAPAN ');
DIFF/D5=ENGLAND-JAPAN;
END
TABLE FILE CAR
SUM ENGLAND JAPAN DIFF
BY SEATS
SUM ENGLAND JAPAN DIFF
BY SEATS
BY CAR
END