Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

count occurrences of a field for two years

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: count occurrences of a field for two years
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject: Reply to: count occurrences of a field for two years
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Count the length of the record & ... sreekusr DFSORT/ICETOOL 3 Thu Mar 23, 2017 7:52 pm
No new posts Splitting a report based on a field abdulrafi DFSORT/ICETOOL 6 Thu Mar 23, 2017 6:56 pm
No new posts SORT VSAM file with each field one by... maxsubrat DFSORT/ICETOOL 6 Tue Mar 14, 2017 1:07 pm
No new posts Display FTP Session Count within TSO Yolanda Harvey TSO/ISPF 3 Fri Mar 10, 2017 10:31 pm
No new posts IMS DB-How to update a record (a sing... Nic Clouston IMS DB/DC 9 Thu Mar 09, 2017 4:38 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us