Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Merge 2 records

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> SYNCSORT
View previous topic :: :: View next topic  
Author Message
sancraig16

New User


Joined: 27 Mar 2018
Posts: 22
Location: usa

PostPosted: Tue Mar 27, 2018 8:17 pm    Post subject: Merge 2 records
Reply with quote

Hello,

I have an input file as given below and have given below the expected output.

Sample Input :
Code:
XXXXXX 1111111
AAAAAA
BBBBBB
CCCCCC
YYYYYYY 30
XXXXXX 2222222
AAAAAA
BBBBBB
CCCCCC
XXXXXX 3333333
AAAAAA
BBBBBB
CCCCCC
YYYYYYY 50
XXXXXX 4444444
AAAAAA
BBBBBB
CCCCCC
YYYYYYY 20

Sample Output
Code:
1111111 30
4444444 20

Criteria for selection :

1. Every Record with XXXXXX should have a matching YYYYYYY
2. YYYYYYY record should have numeric value < 40

"XXXXXX 2222222" record was omitted because there was no matching YYYYYYY record following it . "XXXXXX 4444444" was omitted because the following YYYYYYY record had value 50 (> 30).
EDIT: Code'd
Back to top
View user's profile Send private message

sancraig16

New User


Joined: 27 Mar 2018
Posts: 22
Location: usa

PostPosted: Tue Mar 27, 2018 8:33 pm    Post subject:
Reply with quote

Correction - "XXXXXX 4444444" is not being omitted . It will be
"XXXXXX 2222222" and "XXXXXX 3333333".
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2361
Location: @my desk

PostPosted: Tue Mar 27, 2018 11:57 pm    Post subject:
Reply with quote

sancraig16,

Welcome to the forums, you could do this by defining 'GROUPS' starting with XXXXXX, and PUSH the '1111111' field across the GROUP.

In the output, INCLUDE only 'YYYYYYY' records with a 'good' numeric value, write the 'PUSH'ed field and the numeric field.
Back to top
View user's profile Send private message
sergeyken

Active User


Joined: 29 Apr 2008
Posts: 309
Location: Maryland

PostPosted: Wed Mar 28, 2018 7:18 pm    Post subject:
Reply with quote

Arun Raj wrote:
sancraig16,

Welcome to the forums, you could do this by defining 'GROUPS' starting with XXXXXX, and PUSH the '1111111' field across the GROUP.

In the output, INCLUDE only 'YYYYYYY' records with a 'good' numeric value, write the 'PUSH'ed field and the numeric field.


Extra note:
After 'YYYYYYY' record with 'bad' numeric value the previously PUSHed value needs to be erased to terminate the 'bad' group (or "re-PUSHed", with blanks, or whatever), until new 'XXXXXXX' record is found.
Back to top
View user's profile Send private message
sancraig16

New User


Joined: 27 Mar 2018
Posts: 22
Location: usa

PostPosted: Wed Mar 28, 2018 7:43 pm    Post subject:
Reply with quote

Arun Raj wrote:
sancraig16,

Welcome to the forums, you could do this by defining 'GROUPS' starting with XXXXXX, and PUSH the '1111111' field across the GROUP.

In the output, INCLUDE only 'YYYYYYY' records with a 'good' numeric value, write the 'PUSH'ed field and the numeric field.


Can you please provide an example for defining GROUPS with PUSH.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2019
Location: UK

PostPosted: Wed Mar 28, 2018 9:44 pm    Post subject:
Reply with quote

Quote:
Can you please provide an example for defining GROUPS with PUSH.

Plenty of examples in the forum. Use the Search function. I used keywords GROUP and PUSH and searched only the SYNCSORT section of the forum and about the second result had an example.
Back to top
View user's profile Send private message
sergeyken

Active User


Joined: 29 Apr 2008
Posts: 309
Location: Maryland

PostPosted: Wed Mar 28, 2018 10:26 pm    Post subject:
Reply with quote

sancraig16 wrote:
Arun Raj wrote:
sancraig16,

Welcome to the forums, you could do this by defining 'GROUPS' starting with XXXXXX, and PUSH the '1111111' field across the GROUP.

In the output, INCLUDE only 'YYYYYYY' records with a 'good' numeric value, write the 'PUSH'ed field and the numeric field.


Can you please provide an example for defining GROUPS with PUSH.

Have you ever tried to read any SORT manual?
Code:
 INREC IFTHEN=(WHEN=GROUP,
               BEGIN=(1,7,CH,EQ,C'XXXXXXX'),
               END=(1,7,CH,EQ,C'YYYYYYY'), 
               PUSH=(20:9,7))

Deliberately, I do not include other required statements, needed to complete the job. Please RTFM, or try SEARCHing this forum.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2361
Location: @my desk

PostPosted: Thu Mar 29, 2018 2:16 am    Post subject:
Reply with quote

sergeyken wrote:
Extra note:
After 'YYYYYYY' record with 'bad' numeric value the previously PUSHed value needs to be erased to terminate the 'bad' group (or "re-PUSHed", with blanks, or whatever), until new 'XXXXXXX' record is found.
I don't think this is required. Also you don't really need an END-check.
Back to top
View user's profile Send private message
sergeyken

Active User


Joined: 29 Apr 2008
Posts: 309
Location: Maryland

PostPosted: Thu Mar 29, 2018 6:47 pm    Post subject:
Reply with quote

What's about input like this?
Code:
XXXXXXX 1111111
YYYYYYY 30
YYYYYYY 10
XXXXXXX 2222222
XXXXXXX 3333333
YYYYYYY 50
YYYYYYY 10
XXXXXXX 4444444
YYYYYYY 20
YYYYYYY 10

I expect the output without end-group detection should be
Code:
1111111 30
1111111 10
3333333 10
4444444 20
4444444 10

Though I don't have access to zOS right now to run the test.

I got used to fool-proof coding since my university years.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2361
Location: @my desk

PostPosted: Thu Mar 29, 2018 7:39 pm    Post subject:
Reply with quote

sancraig16 wrote:
Criteria for selection :

1. Every Record with XXXXXX should have a matching YYYYYYY
As per the OPs requirement, XXXXXX may or may not have a matching YYYYYYY. Not the other way around. Your input shows multiple consecutive Y-records which is not in line with OP's sample data.
Back to top
View user's profile Send private message
sancraig16

New User


Joined: 27 Mar 2018
Posts: 22
Location: usa

PostPosted: Thu Mar 29, 2018 11:07 pm    Post subject:
Reply with quote

I was able to get this to work except for 1 character that I am looking to remove from my output file.

I need to replace a character ')' using FINDREP with SPACE and that part fails with duplicate parm on syncsort . Any suggestions?
Code:
INREC IFTHEN=(WHEN=GROUP,
               BEGIN=(8,7,CH,EQ,C'XXXXXXX'),
               END=(6,9,CH,EQ,C'YYYYYYYYY'),
               PUSH=(50:20,23))
OPTION COPY
OUTFIL INCLUDE=(6,9,CH,EQ,C'YYYYYYYYY',AND, -
               (17,1,CH,EQ,C' ',OR,16,2,ZD,LT,40)),
       FINDREP=(IN=C')',OUT=C' '),
                        *
       BUILD=(1:50,23,24:15,22),CONVERT


WER270A  OUTFIL STATEMENT  : DUPLICATE PARM FOUND
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2361
Location: @my desk

PostPosted: Thu Mar 29, 2018 11:26 pm    Post subject:
Reply with quote

sancraig16,

Can you try doing the BUILD and FINDREP in separate IFTHENs like this?
Code:
 OUTFIL INCLUDE=(....),
        IFTHEN=(WHEN=INIT,BUILD...
        IFTHEN=(WHEN=INIT,FINDREP...
Back to top
View user's profile Send private message
sancraig16

New User


Joined: 27 Mar 2018
Posts: 22
Location: usa

PostPosted: Fri Mar 30, 2018 12:49 am    Post subject:
Reply with quote

I changed as suggested but Syncsort does not allow me to add CONVERT to change the dataset to FB. It fails with RDW NOT INCLUDED

Code:
INREC IFTHEN=(WHEN=GROUP,
BEGIN=(8,7,CH,EQ,C'XXXXXXX'),
END=(6,9,CH,EQ,C'YYYYYYYYY'),
PUSH=(50:20,23))
OPTION COPY
OUTFIL INCLUDE=(6,9,CH,EQ,C'YYYYYYYYY',AND, -
(17,1,CH,EQ,C' ',OR,16,2,ZD,LT,40)),
IFTHEN=(WHEN=INIT,BUILD=(1:50,23,24:15,22)),
IFTHEN=(WHEN=INIT,FINDREP=(IN=C')',OUT=C' '))


WER235A  SORTOUT  OUTREC RDW NOT INCLUDED
EDIT: Code'd. Please use Code tags while posting code/sample data.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2361
Location: @my desk

PostPosted: Fri Mar 30, 2018 1:01 am    Post subject: Reply to: Merge 2 records
Reply with quote

sancraig16,

Can you post the complete error message along with your control statements when you attempted to use 'CONVERT'? I don't see it in your last post.
Also include your input an output data set attributes (LRECL, RECFM ..).
Back to top
View user's profile Send private message
sancraig16

New User


Joined: 27 Mar 2018
Posts: 22
Location: usa

PostPosted: Fri Mar 30, 2018 1:17 am    Post subject:
Reply with quote

Manual says CONVERT can be used with FIELDS/BUILD parameter only.Using CONVERT gives JCL error as given below and I removed it .

CAY6040E PARAMETER "CONVERT" IS MUTUALLY EXCLUSIVE WITH EARLIER
PARAMETERS


Input dataset is VBA with LRECL of 125. Trying to create a FB dataset of LRECL45 .

Code:

INREC IFTHEN=(WHEN=GROUP,
BEGIN=(8,7,CH,EQ,C'XXXXXXX'),
END=(6,9,CH,EQ,C'YYYYYYYYY'),
PUSH=(50:20,23))
OPTION COPY
OUTFIL INCLUDE=(6,9,CH,EQ,C'YYYYYYYYY',AND, -
(17,1,CH,EQ,C' ',OR,16,2,ZD,LT,40)),
IFTHEN=(WHEN=INIT,BUILD=(1:50,23,24:15,22)),
IFTHEN=(WHEN=INIT,FINDREP=(IN=C')',OUT=C' '))


WER813I  INSTALLATION OPTIONS IN DDT LOAD LIBRARY WILL BE USED
WER276B  SYSDIAG= 12598112, 32499117, 32499117, 41651540
WER164B  6,896K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,
WER164B     12K BYTES RESERVE REQUESTED, 1,004K BYTES USED
WER146B  20K BYTES OF EMERGENCY SPACE ALLOCATED
WER108I  SORTIN   : RECFM=VBA  ; LRECL=   125; BLKSIZE= 27998
WER073I  SORTIN   : DSNAME=SMD.DATASET.SYSPRINT
WER257I  INREC RECORD LENGTH =   125
WER235A  SORTOUT  OUTREC RDW NOT INCLUDED
WER211B  SYNCSMF  CALLED BY SYNCSORT; RC=0000
WER449I  SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2019
Location: UK

PostPosted: Fri Mar 30, 2018 3:49 am    Post subject:
Reply with quote

CONVERT will not give a JCL error - bad JCL will give a JCL error. JCL knows nothing about utility control statements. Furthermore, SYNCSORT messages begin with WER. The message you posted is neither a JCL message nor a Syncsort message. I suspect it is some sort of JCL checker message. Try running the JCL and give us the real messages.
Back to top
View user's profile Send private message
sancraig16

New User


Joined: 27 Mar 2018
Posts: 22
Location: usa

PostPosted: Fri Mar 30, 2018 4:07 am    Post subject:
Reply with quote

Here's the message with CONVERT used.

Code:

INREC IFTHEN=(WHEN=GROUP,
BEGIN=(8,7,CH,EQ,C'XXXXXXX'),
END=(6,9,CH,EQ,C'YYYYYYYYY'),
PUSH=(50:20,23))
OPTION COPY
OUTFIL INCLUDE=(6,9,CH,EQ,C'YYYYYYYYY',AND, -
(17,1,CH,EQ,C' ',OR,16,2,ZD,LT,40)),
IFTHEN=(WHEN=INIT,BUILD=(1:50,23,24:15,22)),
IFTHEN=(WHEN=INIT,FINDREP=(IN=C')',OUT=C' ')),CONVERT


WER813I  INSTALLATION OPTIONS IN DDT LOAD LIBRARY WILL BE USED
WER276B  SYSDIAG= 12505365, 32633851, 32633851, 41651540
WER164B  6,896K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,
WER164B     12K BYTES RESERVE REQUESTED, 1,004K BYTES USED
WER146B  20K BYTES OF EMERGENCY SPACE ALLOCATED
WER108I  SORTIN   : RECFM=VBA  ; LRECL=   125; BLKSIZE= 27998
WER073I  SORTIN   : DSNAME=SMD.DATASET.SYSPRINT
WER257I  INREC RECORD LENGTH =   125
WER136A  SORTOUT   OUTREC HAS OVERLAPPING FIELDS SPECIFIED
WER211B  SYNCSMF  CALLED BY SYNCSORT; RC=0000
WER449I  SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE


If I remove the CONVERT I get below error

Code:

WER235A  SORTOUT  OUTREC RDW NOT INCLUDED
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2361
Location: @my desk

PostPosted: Fri Mar 30, 2018 7:38 pm    Post subject:
Reply with quote

sancraig16,

Since the input data set is VB, you might want to include the PUSH-ed fields at the beginning of the record at pos-5(considering the RDW).
For your CONVERT, you could do your IFTHEN BUILDs in an OUTREC IFTHEN and do the CONVERT and INCLUDE in the OUTFIL.

This is UNTESTED, but should give you something to start with. Good luck!
Code:
   INREC IFTHEN=(WHEN=INIT,                                 
          BUILD=(1,4,23X,5)),                               
         IFTHEN=(WHEN=GROUP,                               
          BEGIN=(31,7,CH,EQ,C'XXXXXXX'),                   
            END=(29,9,CH,EQ,C'YYYYYYYYY'),                 
           PUSH=(5:20,23))                                 
   OPTION COPY                                             
   OUTREC IFTHEN=(WHEN=INIT,BUILD=(1,4,5,23,38,22)),       
          IFTHEN=(WHEN=INIT,FINDREP=(IN=C')',OUT=C' '))   
   OUTFIL INCLUDE=(29,9,CH,EQ,C'YYYYYYYYY',AND,           
                  (40,1,CH,EQ,C' ',OR,39,2,ZD,LT,40)),
            BUILD=(5,45),CONVERT
Back to top
View user's profile Send private message
sancraig16

New User


Joined: 27 Mar 2018
Posts: 22
Location: usa

PostPosted: Fri Mar 30, 2018 10:44 pm    Post subject:
Reply with quote

It worked. Thank you !!
I added an IFTHEN for FINDREP on the existing INREC to remove the ')'
I then added BUILD with CONVERT to the existing OUTFIL to include only the data I needed.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2361
Location: @my desk

PostPosted: Fri Mar 30, 2018 11:48 pm    Post subject:
Reply with quote

sancraig16 wrote:
It worked. Thank you !!
I added an IFTHEN for FINDREP on the existing INREC to remove the ')'
I then added BUILD with CONVERT to the existing OUTFIL to include only the data I needed.
You're welcome. Thanks for letting know!
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 -> SYNCSORT All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts MXG - Processing Compressed DB2 SMF r... vasanthz All Other Mainframe Topics 2 Thu May 10, 2018 12:47 am
No new posts Extract the records with a PD field's... sudhakar84 DFSORT/ICETOOL 11 Mon Apr 02, 2018 7:26 pm
No new posts Can we merge these two sort cards int... abdulrafi JCL & VSAM 2 Thu Mar 08, 2018 4:03 pm
No new posts Copy set of records based on condition krish.deepu SYNCSORT 2 Thu Mar 08, 2018 11:39 am
No new posts add sequence number for duplicate rec... SRUTHI DRAVIDAMANI COBOL Programming 11 Thu Feb 22, 2018 5:37 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us