I'm so new I squeak, but I'm trying to do an Excel BYTOC on a variable field across two appended files with different dates. With this code the TOC_DESC field to changes when the CAS_REGN_NM changes, but the TOC_DATE within TOC_DESC always has the date from the second TABLE FILE PRINT. I need to create Excel tabs for Each combination of CAS_REGN_NM and RPT_PER_END_DT. Thank you! Ron

Example Data:

CAS_REGN_NM TOC_DATE TOC_DESC RPT_PER_END_DT
Central 3/31/2009 Central - 03/31/2009 3/31/2008 0:00
Eastern 3/31/2009 Eastern - 03/31/2009 3/31/2008 0:00
Western 3/31/2009 Western - 03/31/2009 3/31/2008 0:00
Central 3/31/2009 Central - 03/31/2009 3/31/2009 0:00
Eastern 3/31/2009 Eastern - 03/31/2009 3/31/2009 0:00

Example Code:

DEFINE FILE TCAS_OFC_DMSN
TODTN/YYMD=HDATE(RPT_PER_END_DT ,'YYMD');
TODT/I8=TODTN;
TOCYR/A4=EDIT(PEDT,'9999$$$$');
TOCMO/A2=EDIT(PEDT,'$$$$99$$');
TOCDA/A2=EDIT(PEDT,'$$$$$$99');
TOC_DATE/A10=TOCMO | '/' |TOCDA | '/' | TOCYR;
TOC_DESC/A30= CAS_REGN_NM | ' - ' | TOC_DATE;
END

FILEDEF PERF002X1 DISK PERF002X1.CSV (APPEND
-RUN

TABLE FILE TCAS_OFC_DMSN
PRINT
R_FLD1
R_FLD2
CAS_REGN_NM
RPT_PER_END_DT
TOC_DATE
TOC_DESC
BY TOC_DESC NOPRINT
WHERE PRD_END_DATE EQ '&IVD_OOW_DATE';
WHERE R_FLD1 EQ 1;
ON TABLE HOLD AS PERF002X1
END

TABLE FILE TCAS_OFC_DMSN
PRINT
R_FLD1
R_FLD2
CAS_REGN_NM
RPT_PER_END_DT
TOC_DATE
TOC_DESC
BY TOC_DESC NOPRINT
WHERE PRD_END_DATE EQ '&PRD_END_DT';
ON TABLE HOLD AS PERF002X1
END

TABLE FILE PERF002X1
PRINT
TOC_DESC AS 'Region Name, Period End Date'
R_FLD1 AS '&HEAD1'
R_FLD2 AS '&HEAD2'
BY TOC_DESC NOPRINT
HEADING
ON TABLE SET PAGE-NUM OFF
ON TOC_DESC SUMMARIZE R_FLD1 R_FLD2 AS 'Total For Region '
ON TABLE SUMMARIZE R_FLD1 R_FLD2 AS 'Total '
ON TABLE PCHOLD FORMAT EXL2K BYTOC

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

Updated my signiture info.....

In order to get a separate tab for each unique combination of CAS_REGN_NAM (or TOC_DESC) and RPT_PER_END_DT, those two fields would have to be concatenated together and be placed as your primary sort field. That's the only way the BYTOC functionality would create a separate tab for each. Otherwise you just get a tab for each TOC_DESC.

Darin,
I have concatenated them, but the TOC_DATE is always the last accessed date.

quote:

TOC_DESC/A30= CAS_REGN_NM | ' - ' | TOC_DATE;

Ron,

The sample data and code you posted is not complete.
In the data I miss things like R_FLD1 and 2, PEDT, PRD_END_DATE.
In the code I miss the values for the &'s that you use.
And that makes it a little bit harder to exactly pinpoint your problem.
The following code sort of looks like yours and provides the correct output, at least in my setup (still on 764 for this post).

-* Example master file
EX -LINES 11 EDAPUT MASTER,TCAS_OFC_DMSN,CV,FILE
FILENAME=TESTDATA, SUFFIX=FIX,$
SEGNAME=TEST_DATA, $
  FIELD=CAS_REGN_NM   , ALIAS= ,A07 ,A07, $
  FIELD=DUMMY1        , ALIAS= ,A10, A10, $
  FIELD=DUMMY2        , ALIAS= ,A01 ,A01, $
  FIELD=TOC_DESC      , ALIAS= ,A20 ,A20, $
  FIELD=RPT_PER_END_DT, ALIAS= ,HYYMDS, A15, $
  FIELD=PEDT          , ALIAS= ,A08, A08, $
  FIELD=R_FLD1        , ALIAS= ,A08, A08, $
  FIELD=R_FLD2        , ALIAS= ,A08, A08, $

-*Example Data:
EX -LINES 6 EDAPUT FOCTEMP,TCAS_OFC_DMSN,CV,FILE
Central03/31/2009 Central - 03/31/2009 3/31/2008 0:0020100101TESTDATA12345678
Eastern03/31/2009 Eastern - 03/31/2009 3/31/2008 0:0020100201DATATEST23456789
Western03/31/2009 Western - 03/31/2009 3/31/2008 0:0020100301TSTDAT  34567890
Central03/31/2009 Central - 03/31/2009 3/31/2009 0:0020100401DATTST  45678901
Eastern03/31/2009 Eastern - 03/31/2009 3/31/2009 0:0020100501TESTDAT 56789012

FILEDEF TCAS_OFC_DMSN DISK TCAS_OFC_DMSN.FTM (RECFM V LRECL 77
TYPE TCAS_OFC_DMSN.FTM

-* Example Code:
DEFINE FILE TCAS_OFC_DMSN
TODTN/YYMD=HDATE(RPT_PER_END_DT ,'YYMD');
TODT/I8=TODTN;
TOCYR/A4=EDIT(PEDT,'9999$$$$');
TOCMO/A2=EDIT(PEDT,'$$$$99$$');
TOCDA/A2=EDIT(PEDT,'$$$$$$99');
TOC_DATE/A10=TOCMO | '/' |TOCDA | '/' | TOCYR;
TOC_DESC/A30= CAS_REGN_NM | ' - ' | TOC_DATE;
END

FILEDEF PERF002X1 DISK PERF002X1.CSV (APPEND
-RUN

TABLE FILE TCAS_OFC_DMSN
PRINT 
R_FLD1
R_FLD2
CAS_REGN_NM
RPT_PER_END_DT
TOC_DATE
TOC_DESC
BY TOC_DESC NOPRINT
ON TABLE HOLD AS PERF002X1
END

TABLE FILE TCAS_OFC_DMSN
PRINT 
R_FLD1
R_FLD2
CAS_REGN_NM
RPT_PER_END_DT
TOC_DATE
TOC_DESC
BY TOC_DESC NOPRINT
ON TABLE HOLD AS PERF002X1
END

TABLE FILE PERF002X1
PRINT 
TOC_DESC AS 'Region Name, Period End Date'
R_FLD1
R_FLD2
BY TOC_DESC NOPRINT
ON TABLE SET PAGE-NUM OFF
ON TOC_DESC SUMMARIZE R_FLD1 R_FLD2 AS 'Total For Region '
ON TABLE SUMMARIZE R_FLD1 R_FLD2 AS 'Total '
ON TABLE PCHOLD FORMAT EXL2K BYTOC
END

I get 5 different tabs / worksheets with the correct names displayed on them.

GamP,
Thank you so much for the fast, detailed response! That solution works fine with 1 input file. I am reading an Oracle twice with two different dates and appending the file. The simplified test data would be:


CAS_REGN_NM RPT_PER_END_DT R_FLD1

TABLE FILE 1
Central 3/31/2008 0:00 1
Eastern 3/31/2008 0:00 1
Western 3/31/2008 0:00 1

TABLE FILE 2
Central 3/31/2009 0:00 1
Eastern 3/31/2009 0:00 1
Western 3/31/2009 0:00 1


Simplified Code:

DEFINE FILE TCAS_OFC_DMSN
TODTN/YYMD=HDATE(RPT_PER_END_DT ,'YYMD');
TODT/I8=TODTN;
TOCYR/A4=EDIT(PEDT,'9999$$$$');
TOCMO/A2=EDIT(PEDT,'$$$$99$$');
TOCDA/A2=EDIT(PEDT,'$$$$$$99');
TOC_DATE/A10=TOCMO | '/' |TOCDA | '/' | TOCYR;
TOC_DESC/A30= CAS_REGN_NM | ' - ' | TOC_DATE;
END

(FILEDEF PERF002X1 DISK PERF002X1.CSV APPEND
-RUN

TABLE FILE TCAS_OFC_DMSN (read 1)
PRINT
R_FLD1
CAS_REGN_NM
RPT_PER_END_DT
TOC_DATE
TOC_DESC
BY TOC_DESC NOPRINT
ON TABLE HOLD AS PERF002X1
END

TABLE FILE TCAS_OFC_DMSN (read 2)
PRINT
R_FLD1
CAS_REGN_NM
RPT_PER_END_DT
TOC_DATE
TOC_DESC
BY TOC_DESC NOPRINT
ON TABLE HOLD AS PERF002X1
END

TABLE FILE PERF002X1
PRINT
TOC_DESC AS 'Region Name, Period End Date'
R_FLD1 AS 'Field 1 heading'
BY TOC_DESC NOPRINT
HEADING
ON TABLE SET PAGE-NUM OFF
ON TOC_DESC SUMMARIZE R_FLD1 AS 'Total For Region '
ON TABLE SUMMARIZE R_FLD1 AS 'Total '
ON TABLE PCHOLD FORMAT EXL2K BYTOC

Solved...... It helps the use the correct fields when formatting the date. Thanks to all who have dropped by or responded.

Good for you!
Could you please change the topic to have it also say [solved]?
You can do that by going to your first post in this thread and edit it. It will allow you to change the description.
Thanks!