This fex:

SET PAGE=NOLEAD
TABLE FILE GGSALES
SUM
COMPUTE SALES_LEVEL/A1 = IF DOLLARS LT 160000 THEN '1' ELSE IF DOLLARS GT 190000 THEN '3' ELSE '2';
ACROSS DATE AS ''
BY STCD

WHERE DATE IN (19960701, 19960801)
END

provides this result:

 1996/07/01 1996/08/01 
Store ID SALES_LEVEL SALES_LEVEL
-------- ----------- -----------
R1020    1           1
R1040    1           2
R1041    1           2
R1019    1           2
R1109    1           2
R1244    1           2
R1250    1           3
R1088    2           1
R1100    2           1
R1248    2           2
R1044    3           1
R1200    3           2

Is it possible to tweak the code to get this result in one pass:

Store Count SALES_LEVEL SALES_LEVEL
----------- ----------- -----------
          1 1           1
          5 1           2
          1 1           3
          2 2           1
          1 2           2
          1 3           1
          1 3           2

A count of the stores instead of the store ID's.

Thanks,

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

I'll play around and see if I can figure out something, but at first glance, the issue I see is that you would have to sort on a computed column which is OK (BY TOTAL SALES_LEVEL) EXCEPT for the fact that I don't think you can use a BY TOTAL with an ACROSS in the same phrase, which throws out the possibility of a single pass.

Darin,

I don't mind if the result is not sorted - I'm having trouble calculating the store count column.

Thanks very much,

Right - but in order to get the store count you would have to sum BY the sales level calculations. The BY is needed to get the store breakdown - not necessarily to perform any sorting. I think your only option is to hold your first request and then read the hold file and sort by the sales_level columns.

Darin, this is what I've concluded too. Thanks for looking into it for me.

Cheers,

Francis,

Is the palcement of the columns important? If not try the code below and play with it

 
SET PAGE=NOLEAD
DEFINE FILE GGSALES
SALES_LEVEL/A1 = IF DOLLARS LT 16000 THEN '1' ELSE IF DOLLARS GT 19000 THEN '3' ELSE '2';
DATEA/A1=IF DATE EQ 19960701 THEN '1' ELSE IF DATE EQ 19960801 THEN '2' ELSE '';
STORECD/A10=SALES_LEVEL|'       '|DATEA;
END
TABLE FILE GGSALES
SUM 
     CNT.STCD
BY STORECD AS ''
 
WHERE DATE IN (19960701, 19960801)
END