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

Count the no of occurances of the records and append it


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
pavanchandana

New User


Joined: 05 Sep 2008
Posts: 15
Location: Hyderabad

PostPosted: Mon Apr 20, 2009 10:04 am
Reply with quote

Hi,
Here is my requirement. Input file (FB, Lrecl=80) looks like this:

001 51531324154 NYC
001 51531346454 NYK
001 51531322323 NYC
003 51531324154 NYB
007 51456424154 NYC
007 51531345454 NY3
007 51531345454 NYC
007 51545645644 NYC

Here considering positions 1-3 as key, my requirement is to count the number of occurences of the record. And the count should be appended to the end of the record in the output file.(FB, Lrecl = 82)

Output file should look like:

001 51531324154 NYC 03
001 51531346454 NYK 03
001 51531322323 NYC 03
003 51531324154 NYB 01
007 51456424154 NYC 04
007 51531345454 NY3 04
007 51531345454 NYC 04
007 51545645644 NYC 04

Could nyone pls help me out on this.... Thanks in Advance!!
Back to top
View user's profile Send private message
bipinpeter

Active User


Joined: 18 Jun 2007
Posts: 213
Location: Cochin/Kerala/India

PostPosted: Mon Apr 20, 2009 10:32 am
Reply with quote

Hi pavanchandana,
Here is one way.I dont know any other better way than this.We can do this by two steps.

STEP1
------

//SORTIN DD DSN=infile1
//SORTOUT DD DSN=temfile1
//SYSIN DD *
SORT FIELDS=(17,3,CH,A)
INREC OVERLAY=(21:SEQNUM,3,ZD)
SUM FIELDS=(21,3)
OUTREC FIELDS=(17,7)
/*
STEP2
-------
//SORTJNF1 DD DSN=infile1
//SORTJNF2 DD DSN=tempfile1
//SORTOUT DD DSN=outfile
//SYSIN DD *
SORT FIELDS=(17,3,CH,A)
JOINKEYS FILE=F1,FIELDS=(17,3,A)
JOINKEYS FILE=F2,FIELDS=(1,3,A)
JOIN UNPAIRED
REFORMAT FIELDS=(F1:1,80,F2:5,3)
/*

Bipin Peter
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Mon Apr 20, 2009 10:56 am
Reply with quote

Hi pavanchandana,
Can you please clarify which sort product you are using?
SYCSORT OR DFSORT?

I suppose DFSORT as it is not in JCL forum.

BIPIN your solution looks like it is for syncsort.
Back to top
View user's profile Send private message
pavanchandana

New User


Joined: 05 Sep 2008
Posts: 15
Location: Hyderabad

PostPosted: Mon Apr 20, 2009 11:05 am
Reply with quote

Hi Sambhaji,
We are using both SYNCSORT and DFSORT products ... so any solution may go fine...

Thank You.
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Mon Apr 20, 2009 11:16 am
Reply with quote

Quote:

We are using both SYNCSORT and DFSORT products


quite unusual....... icon_confused.gif
Back to top
View user's profile Send private message
genesis786

Active User


Joined: 28 Sep 2005
Posts: 210
Location: St Katherine's Dock London

PostPosted: Mon Apr 20, 2009 1:48 pm
Reply with quote

Code:

SORT FIELDS=(17,3,CH,A)
INREC OVERLAY=(21:SEQNUM,3,ZD)
SUM FIELDS=(21,3)
OUTREC FIELDS=(17,7)


if i understand right, pavanchandana's requirement says summing based on the key of the record which is first 3 chars. so the frist sort statement should come somewhat like:

Code:

  SORT FIELDS=(1,3,CH,A)     
  INREC OVERLAY=(21:C'001')   
  SUM FIELDS=(21,3,ZD)       
  OUTREC FIELDS=(1,23)       


and the second step

Code:

  SORT FIELDS=(1,3,CH,A)             
  JOINKEYS FILE=F1,FIELDS=(1,3,A)     
  JOINKEYS FILE=F2,FIELDS=(1,3,A)     
  JOIN UNPAIRED                       
  REFORMAT FIELDS=(F1:1,20,F2:21,3)   
Back to top
View user's profile Send private message
pavanchandana

New User


Joined: 05 Sep 2008
Posts: 15
Location: Hyderabad

PostPosted: Mon Apr 20, 2009 2:38 pm
Reply with quote

Hi all.... Thank you so much for ur response .... Ive got the expected solution from these replies... Thanks again!!
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Mon Apr 20, 2009 9:58 pm
Reply with quote

If the order of records within a group is not important, then the following DFSORT 1 pass JCL will give you the desired results.

Code:

//STEP0100 EXEC PGM=SORT                                         
//SYSOUT   DD SYSOUT=*                                           
//SORTIN   DD *                                                   
001 51531324154 NYC                                               
001 51531346454 NYK                                               
001 51531322323 NYC                                               
003 51531324154 NYB                                               
007 51456424154 NYC                                               
007 51531345454 NY3                                               
007 51531345454 NYC                                               
007 51545645644 NYC                                               
//SORTOUT  DD SYSOUT=*                                           
//SYSIN    DD *                                                   
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(81:SEQNUM,2,ZD,RESTART=(1,3)))
  SORT FIELDS=(1,3,CH,A,81,2,CH,D)                               
                                                                 
  OUTREC IFOUTLEN=82,                                             
  IFTHEN=(WHEN=INIT,OVERLAY=(83:SEQNUM,2,ZD,RESTART=(1,3))),     
  IFTHEN=(WHEN=GROUP,BEGIN=(83,2,ZD,EQ,1),PUSH=(81:81,2))         
/*                                                               


If the order of records within the group is important then the following DFSORT/ICETOOL JCL will give the desired results

Code:

//STEP0200 EXEC PGM=ICETOOL                                         
//TOOLMSG  DD SYSOUT=*                                               
//DFSMSG   DD SYSOUT=*                                               
//IN       DD *                                                     
001 51531324154 NYC                                                 
001 51531346454 NYK                                                 
001 51531322323 NYC                                                 
003 51531324154 NYB                                                 
007 51456424154 NYC                                                 
007 51531345454 NY3                                                 
007 51531345454 NYC                                                 
007 51545645644 NYC                                                 
//T1       DD DSN=&&T1,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)           
//OUT      DD SYSOUT=*                                               
//TOOLIN   DD *                                                     
  COPY FROM(IN) USING(CTL1)                                         
  SORT FROM(T1) USING(CTL2)                                         
//CTL1CNTL DD *                                                     
  OUTFIL FNAMES=T1,REMOVECC,BUILD=(1,80,3X),                         
  SECTIONS=(1,3,TRAILER3=(1,3,81:COUNT=(M11,LENGTH=2),C'D'))         
//CTL2CNTL DD *                                                     
  SORT FIELDS=(1,3,CH,A,81,2,CH,D)                                   
  OUTREC IFTHEN=(WHEN=GROUP,BEGIN=(81,2,CH,NE,C' '),PUSH=(81:81,2)) 
  OUTFIL FNAMES=OUT,OMIT=(83,1,CH,EQ,C'D'),BUILD=(1,82)             
/*
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 Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
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
Search our Forums:

Back to Top