Hi Guys,

I have 4 columns: State, Territory, ZIP Code, Population.

The problems that a ZIP code occurs once in a State but can be in multiple territories within that state.

ie
State 1 | Territory 1 | ZIP Code 1234 | Population 100
State 1 | Territory 2 | ZIP Code 4567 | Population 150
State 1 | Territory 3 | ZIP Code 1234 | Population 100
State 1 | Territory 4 | ZIP Code 4567 | Population 150

The table statement would be:

TABLE FILE MYDATA
SUM MAX.POPULATION
BY STATE
BY TERRITORY
BY ZIPCODE
ON TABLE COLUMN-TOTAL
END

The total population for the above table is currently showing as 500 however the true population is actually 250.

I was thinking creating a hold table that is by state by zip code and joining the two tables together and then using a compute to take the original population for the table and the aggregated population for the total.

ie
COMPUTE POPULATION_DISPLAY = IF ROWTYPE EQ TOTAL THEN 2ND.POPULATION ELSE 1ST.POPULATION;

Hopefully this makes sense.

Thanks
Pete

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

If you're not too fussy over what the output looks like, you could try:

TABLE FILE DATA
SUM 
     MAX.POPULATION
BY       STATE
BY       ZIP_CODE                 NOPRINT
PRINT 
         TERRITORY
         ZIP_CODE
BY       STATE
BY       ZIP_CODE                 NOPRINT
ON TABLE COLUMN-TOTAL             AS ''       MAX.POPULATION
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
TYPE=REPORT, COLUMN=STATE, SEQUENCE=1, $
TYPE=REPORT, COLUMN=TERRITORY, SEQUENCE=2, $
TYPE=REPORT, COLUMN=N5, SEQUENCE=3, $ 
TYPE=REPORT, COLUMN=POPULATION, SEQUENCE=4, $ 
ENDSTYLE
END

or if you are:

TABLE FILE DATA
SUM 
     MAX.POPULATION
BY	 TERRITORY
BY       STATE
BY       ZIP_CODE                
ON TABLE HOLD AS H1
END
TABLE FILE DATA
SUM 
     MAX.POPULATION AS TOT_POP
BY       STATE
BY       ZIP_CODE                
ON TABLE HOLD AS H2
END
MATCH FILE H1
PRINT 
       TERRITORY
       ZIP_CODE                
       POPULATION
BY     STATE
RUN
FILE H2
PRINT
       ZIP_CODE                
       TOT_POP
BY     STATE
AFTER MATCH HOLD OLD-OR-NEW
END
TABLE FILE HOLD
PRINT 
     POPULATION
     TOT_POP NOPRINT 
BY  STATE
BY  TERRITORY
BY  ZIP_CODE
ON TABLE SUBFOOT
" <+0> <+0> <TOT.TOT_POP"
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
TYPE=REPORT,
     HEADALIGN=BODY,
$
TYPE=TABFOOTING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=1,
     SIZE=9,
     STYLE=NORMAL,
     WIDTH=.611,
$
TYPE=TABFOOTING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=2,
     SIZE=9,
     STYLE=NORMAL,
     WIDTH=.875,
$
TYPE=TABFOOTING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=3,
     SIZE=9,
     STYLE=NORMAL,
     WIDTH=.806,
$
TYPE=TABFOOTING,
     LINE=1,
     OBJECT=FIELD,
     ITEM=1,
     SIZE=9,
     STYLE=NORMAL,
     JUSTIFY=RIGHT,
     WIDTH=.986,
$
ENDSTYLE
END