IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

count occurrences of a field for two years
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
Suresh Shankarakrishnan

New User


Joined: 11 Jul 2008
Posts: 42
Location: USA
PostPosted: Fri Jul 17, 2009 4:42 pm
 Reply with quote
Help with the following be greatly appreciated.

My requirement is as follows-

1. Input file, LRECL = 138, Fixed block.

2. Column 129-134 has serial number as shown below in the sample records.
Data in columns 1 to 133 not shown and not relevant.

3. Column 135-138 has year. It can have two values or one value. In this example, year is 2009 and 2010.


PT13852009
PT13852009
PT13852010
ST13772009
ST13772009
GG13992009
GG13992009
GG13992010
GG13992010

Requirement is to find the number of occurrences of serial number in column 129-134 ONLY IF it has two values for the year in column 135-138.

4. Expected output listed below-

PT1385 2009 2
PT1385 2010 1
GG1399 2009 2
GG1399 2010 2

a. File length not relevant, but can be 80, and fixed block.
b. In the above example ST1377 is not listed in the output file as it occurs only for one year - 2009
c. Output file can be ordered for SAME serial number and within that the year - does not matter ascending or descending order as shown above.
d. Serial number in Column 129-134 can vary.
e. The year will be 2009 and 2010 this year, next year it will be 2010 and 2011 and so on. So it need not be hard coded, but hard coding will also work, in which case it will be changed once a year.

Thanks and hope the requirement is clear.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Fri Jul 17, 2009 10:13 pm
 Reply with quote
Suresh Shankarakrishnan,

The following DFSORT JCL will give you the desired results.It assumes that you only have any 2 different years.

Code:

//STEP0100 EXEC PGM=SORT                                           
//SYSOUT   DD SYSOUT=*                                             
//SORTIN   DD DSN=Your 138 byte input file,DISP=SHR
//SORTOUT  DD SYSOUT=*                                             
//SYSIN    DD *                                                   
  INREC BUILD=(129,10,7C'0',C'1')                                 
  SORT FIELDS=(1,10,CH,A)                                         
  SUM FIELDS=(11,8,ZD)                                             
  OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(19:SEQNUM,8,ZD,RESTART=(1,6))),
  IFTHEN=(WHEN=GROUP,BEGIN=(19,8,ZD,EQ,1),PUSH=(27:1,18))         
  OUTFIL OMIT=(7,4,CH,EQ,33,4,CH),                                 
  BUILD=(27,6,X,33,4,X,37,8,ZD,/,                                 
         01,6,X,07,4,X,11,8,ZD)                                   
/*


The output of this job is

Code:

GG1399 2009        2
GG1399 2010        2
PT1385 2009        2
PT1385 2010        1
Back to top
View user's profile Send private message
Suresh Shankarakrishnan

New User


Joined: 11 Jul 2008
Posts: 42
Location: USA
PostPosted: Fri Jul 17, 2009 10:42 pm
 Reply with quote
Thanks Skolusu, will let you know. Yes, there are only two years.
Back to top
View user's profile Send private message
Suresh Shankarakrishnan

New User


Joined: 11 Jul 2008
Posts: 42
Location: USA
PostPosted: Fri Jul 17, 2009 11:12 pm
 Reply with quote
Skolusu, Thanks, works like a charm!

Can you please explain the code?
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Sat Jul 18, 2009 2:02 am
 Reply with quote
Suresh Shankarakrishnan,

The logic is quite simple.

Code:

  INREC BUILD=(129,10,7C'0',C'1')     


The above statement will just have the 10 bytes from pos 129 and put in pos 1 and we also add a constant of 1 at pos 11 which will be used for summing

so the input will look like this
Code:

--------------------
  KEY     |CONSTANT|
(1 -10)   |(11-18) |
PT13852009|00000001|
PT13852009|00000001|
PT13852010|00000001|
ST13772009|00000001|
ST13772009|00000001|
GG13992009|00000001|
GG13992009|00000001|
GG13992010|00000001|
GG13992010|00000001|


Now we sort on the first 10 bytes and sum on the constant which will give you the count of each key

Code:

  SORT FIELDS=(1,10,CH,A)                                         
  SUM FIELDS=(11,8,ZD) 


So the output after these statements is follows

Code:

--------------------
  KEY     |SUM VAL |
(1 -10)   |(11-18) |
--------------------
GG13992009|00000002|
GG13992010|00000002|
PT13852009|00000002|
PT13852010|00000001|
ST13772009|00000002|


Now using OUTREC IFTHEN we put a restart sequence number in pos 19 considering only the first 6 bytes as key. So the value will start with 1 and increase by 1 and whenever the key changes it will restart with 1 again
Code:

OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(19:SEQNUM,8,ZD,RESTART=(1,6))),


This is how the output will look like after the statement

Code:

-----------------------------
  KEY     |SUM VAL |SEQNUM  |
(1 -10)   |(11-18) |(19-26) |
-----------------------------
GG13992009|00000002|00000001|
GG13992010|00000002|00000002|
PT13852009|00000002|00000001|
PT13852010|00000001|00000002|
ST13772009|00000002|00000001|


We now check the value at pos 19 and whenever it is 1 we push the value at pos 1 for 18 bytes on to pos 27 for 18 bytes.

Code:

IFTHEN=(WHEN=GROUP,BEGIN=(19,8,ZD,EQ,1),PUSH=(27:1,18))       


This is how the records will look like after the above statement.

Code:

-------------------------------------------------
  KEY     |SUM VAL |SEQNUM  |  KEY     |SUM VAL |
(1 -10)   |(11-18) |(19-26) |(27-36)   |(37-44) |
-------------------------------------------------
GG13992009|00000002|00000001|GG13992009|00000002|
GG13992010|00000002|00000002|GG13992009|00000002|
PT13852009|00000002|00000001|PT13852009|00000002|
PT13852010|00000001|00000002|PT13852009|00000002|
ST13772009|00000002|00000001|ST13772009|00000002|


Now we use an omit condition on OUTFIL and compare the year values and if they are equal then we omit them

Code:

OUTFIL OMIT=(7,4,CH,EQ,33,4,CH),


This is how your your records will look like after the above statement

Code:

-------------------------------------------------
  KEY     |SUM VAL |SEQNUM  |  KEY     |SUM VAL |
(1 -10)   |(11-18) |(19-26) |(27-36)   |(37-44) |
-------------------------------------------------
GG13992010|00000002|00000002|GG13992009|00000002|
PT13852010|00000001|00000002|PT13852009|00000002|


Now using build we write the single record into 2 records. / is used to break the record into 2

Code:

BUILD=(27,6,X,33,4,X,37,8,ZD,/,
         01,6,X,07,4,X,11,8,ZD)


the final result is

Code:

GG1399 2009        2
GG1399 2010        2
PT1385 2009        2
PT1385 2010        1
Back to top
View user's profile Send private message
Suresh Shankarakrishnan

New User


Joined: 11 Jul 2008
Posts: 42
Location: USA
PostPosted: Sat Jul 18, 2009 2:07 am
 Reply with quote
Kolusu, thanks for that explanation!
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 

Similar Topics
Topic Forum Replies
No new posts To get the count of rows for every 1 ... DB2 3
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
No new posts To find whether record count are true... DFSORT/ICETOOL 6
No new posts Validating record count of a file is ... DFSORT/ICETOOL 13
No new posts Exclude rows with > than x occurre... DFSORT/ICETOOL 6
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us