DFSORT Report Trailer using IFTHEN & Count



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

DFSORT Report Trailer using IFTHEN & Count

Postby p_bplayer » Fri Jul 27, 2012 6:53 am

Good afternoon, y'all,

Currently I have a massive fiche file that needs to be broken up into different files. Using DFSORT I am able to do this but I also need to include a summary report that includes a count of how many different instances a report grouping shows up. The code below gives me a syntax error on the IFTHEN statement as well as a DEFINE error on the COUNT operator. In essence I am trying to get a COUNT of when a report type and date are in the same entry. This is the first time I've used DFSORT for reporting so I am unsure if an IFTHEN and a COUNT can be used in reporting. If an ICETOOL would be better used here I'm flexible to do so.

//SORTOUT  EXEC PGM=SORT                           
//TOOLMSG  DD SYSOUT=*                             
//DFSMSG   DD SYSOUT=*                             
//SYSOUT   DD  SYSOUT=*                           
//SYSPRINT DD  SYSOUT=*                           
//KO41SCR  DD  DSN=&DATAO..KO.DISK.DMS.KO41SCR, 
//             DISP=(NEW,CATLG,DELETE),UNIT=DISK,
//             SPACE=(CYL,(60,15),RLSE),         
//             DCB=(BLKSIZE=0,RECFM=FB,LRECL=133)
//KO41HLM  DD  DSN=&DATAO..KO.DISK.DMS.KO41HLM, 
//             DISP=(NEW,CATLG,DELETE),UNIT=DISK,
//             SPACE=(CYL,(60,15),RLSE),         
//             DCB=(BLKSIZE=0,RECFM=FB,LRECL=133)
//KO41MA   DD  DSN=&DATAO..KO.DISK.DMS.KO41MA,   
//             DISP=(NEW,CATLG,DELETE),UNIT=DISK,
//             SPACE=(CYL,(60,15),RLSE),         
//             DCB=(BLKSIZE=0,RECFM=FB,LRECL=133)
//KO41MC   DD  DSN=&DATAO..KO.DISK.DMS.KO41MC,   
//             DISP=(NEW,CATLG,DELETE),UNIT=DISK,
//             SPACE=(CYL,(60,15),RLSE),         
//             DCB=(BLKSIZE=0,RECFM=FB,LRECL=133)
//KO41MD   DD  DSN=&DATAO..KO.DISK.DMS.KO41MD,   
//             DISP=(NEW,CATLG,DELETE),UNIT=DISK,
//             SPACE=(CYL,(60,15),RLSE),         
//             DCB=(BLKSIZE=0,RECFM=FB,LRECL=133)
//DMSSUM   DD  DSN=&DATAO..KO.DISK.DMS.SUMMARY.REG,
//             DISP=(NEW,CATLG,DELETE),           
//             SPACE=(CYL,(80,40),RLSE),UNIT=SYSDA,
//             DCB=(RECFM=FB,LRECL=80,BLKSIZE=0)   
//SORTWK01 DD  UNIT=SYSDA,SPACE=(CYL,(40,10),RLSE)
//SORTWK02 DD  UNIT=SYSDA,SPACE=(CYL,(40,10),RLSE)
//SORTWK03 DD  UNIT=SYSDA,SPACE=(CYL,(40,10),RLSE)
//SORTWK04 DD  UNIT=SYSDA,SPACE=(CYL,(40,10),RLSE)
//SORTWK05 DD  UNIT=SYSDA,SPACE=(CYL,(40,10),RLSE)
//SORTIN   DD  DSN=T.KO.DISK.SIFICH.S41,DISP=SHR   
//SYSIN    DD  *
OPTION COPY                                     
OUTFIL FNAMES=KO41SCR, -                         
   INCLUDE=(3,8,CH,EQ,C'KO41SCR '),BUILD=(78,133)
OUTFIL FNAMES=KO41HLM, -                         
   INCLUDE=(3,8,CH,EQ,C'KO41HLM '),BUILD=(78,133)
OUTFIL FNAMES=KO41MA, -                         
   INCLUDE=(3,8,CH,EQ,C'KO41MA  '),BUILD=(78,133)
OUTFIL FNAMES=KO41MC, -                         
   INCLUDE=(3,8,CH,EQ,C'KO41MC  '),BUILD=(78,133)
OUTFIL FNAMES=KO41MD, -                         
   INCLUDE=(3,8,CH,EQ,C'KO41MD  '),BUILD=(78,133)
OUTFIL FNAMES=DMSSUM,LINES=5,REMOVECC,NODETAIL, -             
HEADER1=(1:'DMS NOTICE SUMMARY REPORT', -                     
30:DATE=(MD4/)), -                                           
TRAILER1=(2/, -                                               
3:'KO41HLM = ', -                                             
IFTHEN=(WHEN=(3,8,CH,EQ,C'KO41HLM ',AND,25,4,CH,EQ,C'DATE'), -
COUNT=(EDIT=(IIIIT)))))))                                     


The input file contains the following sample entries:

  KO41HLM                DATE
  KO41HLM                ACCT
  KO41HLM                AMNT
  KO41HLM                NAME
  KO41SCR                 DATE
  KO41SCR                 ACCT
  KO41SCR                 AMNT
  KO41SCR                 NAME
  KO41HLM                DATE
  KO41HLM                ACCT
  KO41HLM                AMNT
  KO41HLM                NAME
  KO41MA                  DATE
  KO41MA                  ACCT
  KO41MA                  AMNT
  KO41MA                  NAME
  KO41HLM                DATE
  KO41HLM                ACCT
  KO41HLM                AMNT
  KO41HLM                NAME
  KO41MA                  DATE
  KO41MA                  ACCT
  KO41MA                  AMNT
  KO41MA                  NAME
  KO41HLM                DATE
  KO41HLM                ACCT
  KO41HLM                AMNT
  KO41HLM                NAME


The report output should look like this:

DMS NOTICE SUMMARY REPORT     07/26/12
KO41HLM                                      4
KO41SCR                                       1
KO41MA                                        2
KO41MD                                        0


Thanks!
p_bplayer
 
Posts: 5
Joined: Wed Jul 25, 2012 1:33 am
Has thanked: 0 time
Been thanked: 1 time

Re: DFSORT Report Trailer using IFTHEN & Count

 

Re: DFSORT Report Trailer using IFTHEN & Count

Postby BillyBoyo » Fri Jul 27, 2012 1:11 pm

We need to see all the ICE messages from your SYSOUT please.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: DFSORT Report Trailer using IFTHEN & Count

Postby p_bplayer » Fri Jul 27, 2012 7:13 pm

Here you go...sorry 'bout that.

                OUTFIL FNAMES=DMSSUM,LINES=5,REMOVECC,NODETAIL, -             
                HEADER1=(1:'DMS NOTICE SUMMARY REPORT', -                     
                30:DATE=(MD4/)), -                                             
                TRAILER1=(2/, -                                               
                3:'KO41HLM = ', -                                             
                30:IFTHEN=(WHEN=(3,8,CH,EQ,C'KO41HLM ',AND,89,4,CH,EQ,C'DATE'),
                   $                                                           
ICE007A 1 SYNTAX ERROR                                                         
                COUNT=(EDIT=(IIIIT))))))                                       
                $                                                             
ICE005A 0 STATEMENT DEFINER ERROR                                             
ICE751I 0 C5-K90013 C6-K90013 C7-K90000 C8-K90013 E7-K24705                   
ICE052I 3 END OF DFSORT                                                       
p_bplayer
 
Posts: 5
Joined: Wed Jul 25, 2012 1:33 am
Has thanked: 0 time
Been thanked: 1 time

Re: DFSORT Report Trailer using IFTHEN & Count

Postby BillyBoyo » Fri Jul 27, 2012 10:32 pm

Well, you can't just insert IFTHENs where you want.

If you can now describe what you are trying to do, I'm sure someone can help.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: DFSORT Report Trailer using IFTHEN & Count

Postby skolusu » Fri Jul 27, 2012 11:02 pm

p_bplayer,

You cannot use IFTHEN statements on reporting functions like headerx or trailerx. Your JCL is a Mess.

1. You have a weird choice of naming your steps. Why would you name your step as SORTOUT?
2. You don't need the TOOLMSG , DFSMSG , SYPRINT DD's as they are NOT used by PGM=SORT. You would need TOOLMSG and DFSMSG ONLY when you are running ICETOOL
3. You don't need to provide the DCB parameters for SORT, it will automatically calculate based on the sortin or BUILD/OVERLAY statements.
3. You are using COPY function, so there is NO need for SORTWK datasets
4. You don't need the hypen characters as continuation in SYSIN. You would only need them in TOOLIN which is only for ICETOOL.
5. If you want to get a count of all the records with DATE at pos 25 with a default value of zero when it is not found , then you need to use INREC or OUTREC IFTHEN and modify the field and then use that field to total it up using sections. Here is I used the position 30 to get the totals.

Here is the modified JCL
//STEP0100 EXEC PGM=SORT                                 
//SYSOUT   DD  SYSOUT=*                                 
//SORTIN   DD  DSN=T.KO.DISK.SIFICH.S41,DISP=SHR         
//*                                                     
//KO41SCR  DD  DSN=&DATAO..KO.DISK.DMS.KO41SCR,         
//             DISP=(NEW,CATLG,DELETE),UNIT=DISK,       
//             SPACE=(CYL,(60,15),RLSE)                 
//*                                                     
//KO41HLM  DD  DSN=&DATAO..KO.DISK.DMS.KO41HLM,         
//             DISP=(NEW,CATLG,DELETE),UNIT=DISK,       
//             SPACE=(CYL,(60,15),RLSE)                 
//*                                                     
//KO41MA   DD  DSN=&DATAO..KO.DISK.DMS.KO41MA,           
//             DISP=(NEW,CATLG,DELETE),UNIT=DISK,       
//             SPACE=(CYL,(60,15),RLSE)                 
//*                                                     
//KO41MC   DD  DSN=&DATAO..KO.DISK.DMS.KO41MC,           
//             DISP=(NEW,CATLG,DELETE),UNIT=DISK,       
//             SPACE=(CYL,(60,15),RLSE)                 
//*                                                     
//KO41MD   DD  DSN=&DATAO..KO.DISK.DMS.KO41MD,           
//             DISP=(NEW,CATLG,DELETE),UNIT=DISK,       
//             SPACE=(CYL,(60,15),RLSE)                 
//*                                                     
//DMSSUM   DD  DSN=&DATAO..KO.DISK.DMS.SUMMARY.REG,     
//             DISP=(NEW,CATLG,DELETE),                 
//             SPACE=(CYL,(80,40),RLSE),UNIT=SYSDA       
//*                                                     
//SYSIN    DD *                                                     
  OPTION COPY                                                       
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(30:C'0')),                       
  IFTHEN=(WHEN=(25,4,CH,EQ,C'DATE'),OVERLAY=(30:C'1'))               
                                                                     
  OUTFIL FNAMES=KO41SCR,BUILD=(78,133),INCLUDE=(3,8,CH,EQ,C'KO41SCR')
  OUTFIL FNAMES=KO41HLM,BUILD=(78,133),INCLUDE=(3,8,CH,EQ,C'KO41HLM')
  OUTFIL FNAMES=KO41MA,BUILD=(78,133),INCLUDE=(3,8,CH,EQ,C'KO41MA') 
  OUTFIL FNAMES=KO41MC,BUILD=(78,133),INCLUDE=(3,8,CH,EQ,C'KO41MC') 
  OUTFIL FNAMES=KO41MD,BUILD=(78,133),INCLUDE=(3,8,CH,EQ,C'KO41MD') 
                                                                     
  OUTFIL FNAMES=DMSSUM,REMOVECC,NODETAIL,                           
  HEADER2=(1:'DMS NOTICE SUMMARY REPORT',30:DATE=(MD4/),2/),         
  SECTIONS=(3,8,                                                     
  TRAILER3=(3:3,8,5X,TOT=(30,1,ZD,M10,LENGTH=5)))                   
//*


The following is the OUTPUT from the reporting file
DMS NOTICE SUMMARY REPORT    07/27/2012       
                                               
  KO41HLM          2                           
  KO41SCR          1                           
  KO41HLM          1                           
  KO41MA           0                           
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: DFSORT Report Trailer using IFTHEN & Count

Postby p_bplayer » Sat Jul 28, 2012 2:08 am

Thanks for the response. That being said, I don't think I did a good job of explaining what I am trying to accomplish. Currently we are using an eztrieve job to read a massive file that contains late payment notices and internal reports. Each type of notice and report is indicated by a phase name i.e. KO41HLM, KO41MA, KO41MC..etc....

I only need to pull certain phase names out of this file and write them out to an output file. We are trying to discontinue our usage of eztrieve, hence the switch to either ICETOOl or DFSORT to do the same processing. That is the reason why the JCL is a mess. It was pulled from an existing job that uses eztrieve. I originally was trying to use ICETOOL and that is the reason for the TOOLMSG, DFSMG, and any other extemporaneous JCL that you may notate as being incorrect or not needed. I got a little further with DFSORT on the Summation Report so I kept using that. The summation report should consist of a total count of each INSTANCE of a notice where the row contains a phase name (i.e. KO41HLM) and another variable (it could be anything just that DATE is always going to be on a customer late payment notice in the same location for all notices). I don't need a total of everything in the file since I am not sending a report out for printing. Just the notices that I am pulling out and sending in a file for printing to an outside company. The summation report should match the number of customer notices being sent out by the third party printer. Below is the output when I ran your JCL....
DMS NOTICE SUMMARY RE
                     
  KO41NP            0
  KO41QD            0
  KO41NP            0
  KO41FCT          0
  KO41CV1          616
  KO41CV3          224
  KO41CV5          145
  KO41CV6          17
  KO41CV11        14
  KO41CV8          57
  KO41CV10        50
  KO41HLM         0
  KO41CV6          63
  KO41HLM         0
  KO41NP            0
  KO41QD           0


KO41HLM occurs more than once and it should be a total number of all instances where 3,8 equals 'KO41HLM' and 25,4 equals 'DATE'. I need to bypass such phase names as KO41NP and KO41QD as these are not being printed out so I don't need to reconcile a total.

Hopefully this clarifies a little more. Now, how do I get a total count for each notice that I send out all in one section or grouping?
p_bplayer
 
Posts: 5
Joined: Wed Jul 25, 2012 1:33 am
Has thanked: 0 time
Been thanked: 1 time

Re: DFSORT Report Trailer using IFTHEN & Count

Postby skolusu » Sat Jul 28, 2012 3:35 am

p_bplayer wrote:Thanks for the response. That being said, I don't think I did a good job of explaining what I am trying to accomplish. Currently we are using an eztrieve job to read a massive file that contains late payment notices and internal reports. Each type of notice and report is indicated by a phase name i.e. KO41HLM, KO41MA, KO41MC..etc....
KO41HLM occurs more than once and it should be a total number of all instances where 3,8 equals 'KO41HLM' and 25,4 equals 'DATE'. I need to bypass such phase names as KO41NP and KO41QD as these are not being printed out so I don't need to reconcile a total.

Hopefully this clarifies a little more. Now, how do I get a total count for each notice that I send out all in one section or grouping?


Well the reason you are getting wrong results is that your Input is NOT sorted on the phase names KO41HLM, KO41MA, KO41MC... Since you are only interested in few phases from the total input, you can use an INCLUDE COND to filter only these phase records.
  INCLUDE COND=(3,8,CH,EQ,C'KO41SCR',OR,     
                3,8,CH,EQ,C'KO41HLM',OR,     
                3,8,CH,EQ,C'KO41MA',OR,     
                3,8,CH,EQ,C'KO41MC',OR,     
                3,8,CH,EQ,C'KO41MD')         


If you want a summary report of the phases you need to sort the file on the phase names. I added the dynamic allocation so that you don't have to code the JCL sortworks. So use the following control cards.

//SYSIN    DD *                                                       
  OPTION DYNALLOC=(SYSDA,6)                                           
  SORT FIELDS=(3,8,CH,A)                                             
  INCLUDE COND=(3,8,CH,EQ,C'KO41SCR',OR,                             
                3,8,CH,EQ,C'KO41HLM',OR,                             
                3,8,CH,EQ,C'KO41MA',OR,                             
                3,8,CH,EQ,C'KO41MC',OR,                             
                3,8,CH,EQ,C'KO41MD')                                 
                                                                     
  OUTFIL FNAMES=KO41SCR,BUILD=(78,133),INCLUDE=(3,8,CH,EQ,C'KO41SCR')
  OUTFIL FNAMES=KO41HLM,BUILD=(78,133),INCLUDE=(3,8,CH,EQ,C'KO41HLM')
  OUTFIL FNAMES=KO41MA,BUILD=(78,133),INCLUDE=(3,8,CH,EQ,C'KO41MA')   
  OUTFIL FNAMES=KO41MC,BUILD=(78,133),INCLUDE=(3,8,CH,EQ,C'KO41MC')   
  OUTFIL FNAMES=KO41MD,BUILD=(78,133),INCLUDE=(3,8,CH,EQ,C'KO41MD')   
                                                                     
  OUTFIL FNAMES=DMSSUM,REMOVECC,NODETAIL,BUILD=(80X),                 
  INCLUDE=(25,4,CH,EQ,C'DATE'),                                       
  HEADER2=('DMS NOTICE SUMMARY REPORT',30:DATE=(MD4/),2/),           
  SECTIONS=(3,8,                                                     
  TRAILER3=(3:3,8,5X,COUNT=(M10,LENGTH=5)))                           
//*
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: DFSORT Report Trailer using IFTHEN & Count

Postby p_bplayer » Sun Jul 29, 2012 2:22 am

I can't sort on the phase name....the groupings of phase names must stay in a particular order as you can see by the below file input....this is a notice and gets sent to the printing company as written out in the file.

  KO41HLM          DATE: 07/27/12                                                   
  KO41HLM                                             
  KO41HLM          THOMAS  KINCAID                                         
  KO41HLM          1120 N 7TH AVE                                                   
  KO41HLM          SOMEPLACE, NY                                                                                                               
  KO41HLM                                                                                                     
  KO41HLM          Account Number: 000000-000-000-00                                                                         
  KO41HLM                                                 
  KO41HLM          As of the date of this letter, your required payment on the loan 
  KO41HLM          described above is past due by more than thirty(30) days. Since   
  KO41HLM          this loan is secured by residential property which may be your   
  KO41HLM          principal dwelling, we are required to advise you that you etc...

The above listing/group of KO41HLM consists of ONE notice...I need to be able to copy out and then count the total number of notices in one pass preferably.

These users thanked the author p_bplayer for the post:
Gunasekaran K (Wed Jan 09, 2013 7:56 pm)
p_bplayer
 
Posts: 5
Joined: Wed Jul 25, 2012 1:33 am
Has thanked: 0 time
Been thanked: 1 time

Re: DFSORT Report Trailer using IFTHEN & Count

Postby skolusu » Mon Jul 30, 2012 9:23 pm

p_bplayer wrote:I can't sort on the phase name....the groupings of phase names must stay in a particular order as you can see by the below file input....this is a notice and gets sent to the printing company as written out in the file.


You can add EQUALS parm which would retain the order of the duplicate records. Since you are only interested in few groups of records , you can actually get the desired results using a COPY function.

Use the following Control cards.

//SYSIN    DD *                                                         
  OPTION COPY                                                           
  INCLUDE COND=(03,8,CH,EQ,C'KO41SCR',OR,                               
                03,8,CH,EQ,C'KO41HLM',OR,                               
                03,8,CH,EQ,C'KO41MA',OR,                               
                03,8,CH,EQ,C'KO41MC',OR,                               
                03,8,CH,EQ,C'KO41MD')                                   
                                                                       
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(30:5C'0')),                         
  IFTHEN=(WHEN=(3,8,CH,EQ,C'KO41SCR',AND,25,4,CH,EQ,C'DATE'),           
  OVERLAY=(30:C'1')),                                                   
  IFTHEN=(WHEN=(3,8,CH,EQ,C'KO41HLM',AND,25,4,CH,EQ,C'DATE'),           
  OVERLAY=(31:C'1')),                                                   
  IFTHEN=(WHEN=(3,8,CH,EQ,C'KO41MA',AND,25,4,CH,EQ,C'DATE'),           
  OVERLAY=(32:C'1')),                                                   
  IFTHEN=(WHEN=(3,8,CH,EQ,C'KO41MC',AND,25,4,CH,EQ,C'DATE'),           
  OVERLAY=(33:C'1')),                                                   
  IFTHEN=(WHEN=(3,8,CH,EQ,C'KO41MD',AND,25,4,CH,EQ,C'DATE'),           
  OVERLAY=(34:C'1'))                                                   
                                                                       
  OUTFIL FNAMES=KO41SCR,BUILD=(78,133),INCLUDE=(3,8,CH,EQ,C'KO41SCR')   
  OUTFIL FNAMES=KO41HLM,BUILD=(78,133),INCLUDE=(3,8,CH,EQ,C'KO41HLM')   
  OUTFIL FNAMES=KO41MA,BUILD=(78,133),INCLUDE=(3,8,CH,EQ,C'KO41MA')     
  OUTFIL FNAMES=KO41MC,BUILD=(78,133),INCLUDE=(3,8,CH,EQ,C'KO41MC')     
  OUTFIL FNAMES=KO41MD,BUILD=(78,133),INCLUDE=(3,8,CH,EQ,C'KO41MD')     
                                                                       
  OUTFIL FNAMES=DMSSUM,REMOVECC,NODETAIL,BUILD=(80X),                   
  HEADER1=('DMS NOTICE SUMMARY REPORT',30:DATE=(MD4/),2/),             
  TRAILER1=(3:C'KO41SCR',5X,TOT=(30,1,ZD,M10,LENGTH=5),/,               
            3:C'KO41HLM',5X,TOT=(31,1,ZD,M10,LENGTH=5),/,               
            3:C'KO41MA ',5X,TOT=(32,1,ZD,M10,LENGTH=5),/,               
            3:C'KO41MC ',5X,TOT=(33,1,ZD,M10,LENGTH=5),/,               
            3:C'KO41MD ',5X,TOT=(34,1,ZD,M10,LENGTH=5))                 
//*
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: DFSORT Report Trailer using IFTHEN & Count

Postby p_bplayer » Tue Jul 31, 2012 2:18 am

Sweet! Got the correct results and built the files accordingly. Thanks so much for the help on this!
p_bplayer
 
Posts: 5
Joined: Wed Jul 25, 2012 1:33 am
Has thanked: 0 time
Been thanked: 1 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post