Page 1 of 1

Adding seq number to changing acct num and record count

PostPosted: Mon Jun 03, 2013 6:44 pm
by maijiri
I’m a novice when it comes to sync sort. I tried searching for an answer to my question, but it has been to no avail. I’m trying to group/add the same sequence number based on an account number. I got that part working, but I also need to limit the number per group to 7. I need the sequence number to increment at the account number change and when the number of records equals 7. Thanks in advance!

Here is what I have so far.

Input file…

000000000000001
000000000000002
000000000000003
000000000000004
000000000000004
000000000000004
000000000000004
000000000000004
000000000000004
000000000000004
000000000000004
000000000000004
000000000000004
000000000000005
000000000000006
000000000000007
000000000000008

SORT FIELDS=(132,15,CH,A)
OUTREC IFTHEN=(WHEN=INIT,RECORDS=7,OVERLAY=(3301:SEQNUM,8,ZD,
RESTART=(132,15))),
IFTHEN=(WHEN=GROUP,BEGIN=(3301,8,ZD,EQ,1),PUSH=(3290:ID=8))
OUTFIL BUILD=(1,3300)


Current output:

00000001
00000002
00000003
00000004
00000004
00000004
00000004
00000004
00000004
00000004
00000004
00000004
00000004
00000005
00000006
00000007
00000008


Desired output:

00000001
00000002
00000003
00000004
00000004
00000004
00000004
00000004
00000004
00000005
00000005
00000005
00000005
00000006
00000007
00000008
00000009

Re: Adding seq number to changing acct num and record count

PostPosted: Mon Jun 03, 2013 6:52 pm
by NicC
Your problem is that you cannot read or you are confused - you say you use Syncsort yet you post in the DFSort part of the forum. I will move to Syncsort.

Re: Adding seq number to changing acct num and record count

PostPosted: Mon Jun 03, 2013 7:19 pm
by maijiri
Thanks...sorry about that NicC.

Re: Adding seq number to changing acct num and record count

PostPosted: Mon Jun 03, 2013 10:26 pm
by bodatrinadh
Try this code...
 
//STEP00 EXEC PGM=SORT
//SYSPRINT DD SYSOUT=*
//SYSOUT   DD SYSOUT=*
//SORTIN   DD *
000000000000001
000000000000002
000000000000003
000000000000004
000000000000004
000000000000004
000000000000004
000000000000004
000000000000004
000000000000004
000000000000004
000000000000004
000000000000004
000000000000005
000000000000006
000000000000007
000000000000008
//SORTOUT   DD DSN=&&OUT01,DISP=(,PASS,DELETE),UNIT=SYSDA,
// SPACE=(TRK,(20,5),RLSE)
//SYSIN    DD *
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(81:SEQNUM,2,ZD,RESTART=(1,15))),
        IFTHEN=(WHEN=GROUP,BEGIN=(81,2,ZD,EQ,+8),
                           END=(81,2,ZD,EQ,+1),PUSH=(83:ID=2))
  SORT FIELDS=(1,15,CH,D,81,2,ZD,D)
  OUTREC IFTHEN=(WHEN=GROUP,BEGIN=(81,2,ZD,LT,+7,&,83,2,FS,EQ,NUM),
                           END=(81,2,ZD,EQ,+8),PUSH=(01:1,15))
//*                             
//STEP01 EXEC PGM=SORT
//SYSPRINT DD SYSOUT=*
//SYSOUT   DD SYSOUT=*
//SORTIN DD DSN=&&OUT01,DISP=SHR
//SORTOUT DD SYSOUT=*
//SYSIN   DD *
  SORT FIELDS=(1,15,CH,A)
  OUTREC BUILD=(8,8,72X)



Re: Adding seq number to changing acct num and record count

PostPosted: Mon Jun 03, 2013 11:37 pm
by maijiri
Unfortunately, that didn't work the way I hoped. An example would be if account number 12345 has 22 records, then there should be 4 sets of sequence numbers. 1-7=seq1, 8-14=seq2, 15-21=seq3 and seq4 would only be one record. Your thoughts?

Re: Adding seq number to changing acct num and record count

PostPosted: Tue Jun 04, 2013 2:32 am
by BillyBoyo
Please don't send Private Messages unless you want to pay a consultancy free.

I don't know how you have RECORDS=7 in WHEN=INIT.

Having got the sequence number, with a RESTART, you need to define a GROUP by testing the sequence number after MOD by seven being equal to one and apply your final sequence number from that GROUP.

Re: Adding seq number to changing acct num and record count

PostPosted: Tue Jun 04, 2013 2:42 am
by maijiri
My apologies. First time posting a thread on this site. Thanks for the suggestion. I will research and see if I can get that that to work.

Re: Adding seq number to changing acct num and record count

PostPosted: Tue Jun 04, 2013 3:39 am
by BillyBoyo
OK. If you can't get all of it, post the problem here. If you get all of it, post the solution, please, as it may help someone with a similar requirement in the future.

Re: Adding seq number to changing acct num and record count

PostPosted: Tue Jun 04, 2013 6:13 am
by bodatrinadh
An example would be if account number 12345 has 22 records, then there should be 4 sets of sequence numbers. 1-7=seq1, 8-14=seq2, 15-21=seq3 and seq4 would only be one record. Your thoughts?


And what is the expected output?

Re: Adding seq number to changing acct num and record count

PostPosted: Wed Jun 05, 2013 3:28 am
by maijiri
Unfortunately, I am at my wits end and having difficulty understanding the logic with DIV and MOD operators.

To recap, I need to process a file each day that can have varing numbers of records per account. I need to take that file, group by account number and add a unique sequence number to each group of records. This part I have gotten to work so far.

The second piece of the puzzle is I cannot have more than 7 records per each group. That being the case, I need to regroup and resequence the output. An example would be if acct# 12345 has 200 records, My first sequencing would only give me #1 for all 200 records. The second sequencing would give me sequence numbers 1-29 = 28 groups of 7 and 1 group of 4 (remainding accounts). Here is the code I have so far:

SORT FIELDS=(132,15,CH,A)
OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(3301:SEQNUM,8,ZD,
RESTART=(132,15))),
IFTHEN=(WHEN=GROUP,BEGIN=(3301,8,ZD,EQ,1),PUSH=(3290:ID=8))
OUTFIL BUILD=(1,3300)

Any insight anyone can provide on how to solve this would be truly appreciated. Thanks again!!!

Sincerely,

The Novice :o)