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
 

 

Sort card to concatenate two records based on one condition
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 159
Location: India

PostPosted: Thu Apr 23, 2015 2:53 pm    Post subject: Sort card to concatenate two records based on one condition
Reply with quote

Hi,

Let me show my requirement below first so that it will be easy to understand.

Code:


Input file1:

1      AAAAAAAA        BBBBBB           CCCCCCC
                       B1B1B1   
                       B2B2B2
                       B3B3B3
2      DDDDDDDD        EEEEEEE          FFFFFFFFFF
                                        F1F1F1F1F
3      GGGGGGG         HHHHHH           IIIIIIIIIII


Input file2:

Code:
AAAAAAAA
DDDDDDDD

Output file expected is:

Code:
 AAAAAAAA        BBBBBB           CCCCCCC
                 B1B1B1   
                 B2B2B2
                 B3B3B3
 DDDDDDDD        EEEEEEE          FFFFFFFFFF
                                  F1F1F1F1F

 


I used Joinkeys in sort card like below:

Code:



//STEP1       EXEC  PGM=SORT
//SORTJNF1  DD     DSN=inputfile1,SISP=SHR
//SORTJNF2  DD     DSN=inputfile2,DISP=SHR
//SORTOUT   DD     DSN=output file, DISP=(NEW,CATLG,DELETE)
//SYSOUT     DD     SYSOUT=*
//SYSIN        DD     *
 SORT FIELDS=COPY
 JOINKEYS FILE=F1,FIELDS=(7,8,A)
 JOINKEYS FILE=F2,FIELDS=(1,8,A)
 REFORMAT FIELDS=(F1:1,133)
 OUTREC FIELDS=(1:1,133)
/*

 


The output that I am getting for the above sort card is :

Code:


1      AAAAAAAA        BBBBBB           CCCCCCC
2      DDDDDDDD        EEEEEEE          FFFFFFFFFF

 


But that was the expected output which I need. Can some please suggest me any idea of how to get the expected output.
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7225

PostPosted: Thu Apr 23, 2015 3:10 pm    Post subject: Reply to: Sort card to concatenate two records based on one condition
Reply with quote

You don't show the expected output, so we don't know what you want.

If the data is in order by the key you are using, you should specify SORTED,NOSEQCK on the JOINKEYS where resequencing is not required.
Back to top
View user's profile Send private message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 159
Location: India

PostPosted: Thu Apr 23, 2015 3:12 pm    Post subject: Re: Reply to: Sort card to concatenate two records based on one condition
Reply with quote

Bill Woodger wrote:
You don't show the expected output, so we don't know what you want.

If the data is in order by the key you are using, you should specify SORTED,NOSEQCK on the JOINKEYS where resequencing is not required.


I have showed the expected output in the first code after the input file2


Expected output :

Code:

Output file expected is:

 AAAAAAAA        BBBBBB           CCCCCCC
                 B1B1B1   
                 B2B2B2
                 B3B3B3
 DDDDDDDD        EEEEEEE          FFFFFFFFFF
                                  F1F1F1F1F

Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7225

PostPosted: Thu Apr 23, 2015 4:09 pm    Post subject: Reply to: Sort card to concatenate two records based on one condition
Reply with quote

OK, I missed that, so I've clarified the Codeing of your question.

You need to use INREC IFTHEN=(WHEN=GROUP on JNF1CNTL to PUSH the key onto each record in the group and use that key as the JOINKEYS key. Your BEGIN would be the key not being blank.

Where you extend the record to include the key-for-all depends on whether your data is fixed-length or variable length.

Then do everything you need in the REFORMAT statement for your output (drop off the key from every record), so you don't need OUTREC FIELDS (see here, by the way) (or INREC BUILD, OUTREC BUILD or OUTFIL BUILD).
Back to top
View user's profile Send private message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 159
Location: India

PostPosted: Thu Apr 23, 2015 4:52 pm    Post subject: Re: Reply to: Sort card to concatenate two records based on one condition
Reply with quote

Bill Woodger wrote:
OK, I missed that, so I've clarified the Codeing of your question.

You need to use INREC IFTHEN=(WHEN=GROUP on JNF1CNTL to PUSH the key onto each record in the group and use that key as the JOINKEYS key. Your BEGIN would be the key not being blank.

Where you extend the record to include the key-for-all depends on whether your data is fixed-length or variable length.

Then do everything you need in the REFORMAT statement for your output (drop off the key from every record), so you don't need OUTREC FIELDS (see here, by the way) (or INREC BUILD, OUTREC BUILD or OUTFIL BUILD).


Hi,
Sorry to ask you this but I am new to this IFTHEN and BEGIN i couldnt clearly understand the idea which you gave me. Can you explain me with some codes.

You explained about the JNF1CNTL where should it be included in the sort card containing the JOINKEYS and what value should be given for PUSH and BEGIN should contain the key means, whatever i have specified in the JOINKEYS F1 should I be using that in the BEGIN. how does this compare with input file2 here and the key position differs for the two file as well....
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Thu Apr 23, 2015 6:17 pm    Post subject: Reply to: Sort card to concatenate two records based on one condition
Reply with quote

Quote:
Sorry to ask you this but I am new to this IFTHEN and BEGIN i couldnt clearly understand the idea which you gave me. Can you explain me with some codes.

Swathi,

If you try it, I am sure you'll definitely understand what Bill means.

First try using IFTHEN=(WHEN=GROUP and see what you get as the output. When you see it, you'll know how to use it with JoinKeys
.
Back to top
View user's profile Send private message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 159
Location: India

PostPosted: Thu Apr 23, 2015 6:23 pm    Post subject: Re: Reply to: Sort card to concatenate two records based on one condition
Reply with quote

RahulG31 wrote:
Quote:
Sorry to ask you this but I am new to this IFTHEN and BEGIN i couldnt clearly understand the idea which you gave me. Can you explain me with some codes.

Swathi,

If you try it, I am sure you'll definitely understand what Bill means.

First try using IFTHEN=(WHEN=GROUP and see what you get as the output. When you see it, you'll know how to use it with JoinKeys
.


Ok I will try but I have no idea what fields should be used in begin push.... will try and let you know. ....
Back to top
View user's profile Send private message
rinsio

New User


Joined: 16 Feb 2015
Posts: 13
Location: Madrid, Spain

PostPosted: Thu Apr 23, 2015 7:25 pm    Post subject:
Reply with quote

Hello Swathi Muralidharan,

Here an approach of your request. But we need the last step: drop off the key in the following records of each group.

Code:

//SORTOUT  DD DSN=XXXXXX.MAINFR.SALIDA,                     
//            DISP=(,CATLG,DELETE),                         
//            SPACE=(TRK,(50,50),RLSE),                     
//            DCB=(RECFM=FB,LRECL=80,BLKSIZE=0,DSORG=PS)     
//SYSIN    DD *                                             
    JOINKEYS F1=IN1,FIELDS=(01,8,A)                         
    JOINKEYS F2=IN2,FIELDS=(01,8,A)                   
    REFORMAT FIELDS=(F1:1,80)                               
                                                             
    OPTION COPY                                             
/*                                                           
//JNF1CNTL DD *                                             
    INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,8,CH,NE,C'        '), 
    PUSH=(1:1,8))                                           
/*                                                           



Code:

 BROWSE    XXXXXX.MAINFR.SALIDA                       Line 00000000
 Command ===>                                                  Scrol
********************************* Top of Data **********************
AAAAAAAA       BBBBBB       CCCCCC                                 
AAAAAAAA       B1B1B1                                               
AAAAAAAA       B2B2B2                                               
AAAAAAAA       B3B3B3                                               
DDDDDDDD       EEEEEEE      FFFFFF                                 
DDDDDDDD                    F1F1F1F1                               
******************************** Bottom of Data *******************


Regards
Back to top
View user's profile Send private message
Garry Carroll

Active Member


Joined: 08 May 2006
Posts: 988
Location: Dublin, Ireland / Edinburgh, Scotland

PostPosted: Thu Apr 23, 2015 7:57 pm    Post subject:
Reply with quote

Much better to PUSH the key field into, say, byte 134.
Code:
PUSH=(134:1,8))


The REFORMAT as coded will then drop the bytes after 133.

Garry.
Back to top
View user's profile Send private message
Garry Carroll

Active Member


Joined: 08 May 2006
Posts: 988
Location: Dublin, Ireland / Edinburgh, Scotland

PostPosted: Thu Apr 23, 2015 8:01 pm    Post subject:
Reply with quote

.. sorry, should have mentioned that the JOINKEYS F1 needs adjustment to specify the key in 134,8 since the key to match is now moved.

Garry
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7225

PostPosted: Thu Apr 23, 2015 8:06 pm    Post subject: Reply to: Sort card to concatenate two records based on one condition
Reply with quote

Swathi Muralidharan,

If you're new to the functionality of any part of DFSORT, there is a good "Getting Started" manual available. It contains an example of using a JNFnCNTL file, and, separately, examples of WHEN=GROUP.

Since you are using File 2 as a key-file to extract from, you want you original records unchanged, so put the key from the PUSH in a new location which doesn't exist on your record. After the JOINKEYS, just remove that new key with the REFORMAT.

Variable-length records should be extended at the beginning, and fixed-length records extended at the end.

For fixed-length 80-byte records:
Code:
 PUSH=(81:1,8)


And then:

Code:
 REFORMAT FIELDS=(F1:1,80)


For variable-length records:

Code:
  INREC IFTHEN=(WHEN=NONE,
                BUILD=(1,4,8X,5),


Then:

Code:
 PUSH=(5:1,8)


And

Code:
  REFORMAT FIELDS=(F1:1,4,9)
Back to top
View user's profile Send private message
rinsio

New User


Joined: 16 Feb 2015
Posts: 13
Location: Madrid, Spain

PostPosted: Thu Apr 23, 2015 8:09 pm    Post subject:
Reply with quote

Great Garry,
In the case the record lentgh is 80.

This is the code:

Code:

 //SYSIN    DD *                                               
     JOINKEYS F1=IN1,FIELDS=(73,8,A)                           
     JOINKEYS F2=IN2,FIELDS=(01,8,A)                           
     REFORMAT FIELDS=(F1:1,72)                                 
     OUTREC FIELDS=(1,72,8X)                                   
     OPTION COPY                                               
 /*                                                             
 //JNF1CNTL DD *                                               
     INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,8,CH,NE,C'        '),   
     PUSH=(73:1,8))                                             
 /*                                                             


Code:

 BROWSE    XXXXXX.MAINFR.SALIDA                       Line 00000000 Col 001 080
 Command ===>                                                  Scroll ===> CSR 
********************************* Top of Data **********************************
AAAAAAAA       BBBBBB       CCCCCC                                             
               B1B1B1                                                           
               B2B2B2                                                           
               B3B3B3                                                           
DDDDDDDD       EEEEEEE      FFFFFF                                             
                            F1F1F1F1                                           
******************************** Bottom of Data ********************************


Thanks
Regards
Back to top
View user's profile Send private message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 159
Location: India

PostPosted: Thu Apr 23, 2015 8:15 pm    Post subject: Re: Reply to: Sort card to concatenate two records based on one condition
Reply with quote

Bill Woodger wrote:
Swathi Muralidharan,

If you're new to the functionality of any part of DFSORT, there is a good "Getting Started" manual available. It contains an example of using a JNFnCNTL file, and, separately, examples of WHEN=GROUP.

Since you are using File 2 as a key-file to extract from, you want you original records unchanged, so put the key from the PUSH in a new location which doesn't exist on your record. After the JOINKEYS, just remove that new key with the REFORMAT.

Variable-length records should be extended at the beginning, and fixed-length records extended at the end.

For fixed-length 80-byte records:
Code:
 PUSH=(81:1,8)


And then:

Code:
 REFORMAT FIELDS=(F1:1,80)


For variable-length records:

Code:
  INREC IFTHEN=(WHEN=NONE,
                BUILD=(1,4,8X,5),


Then:

Code:
 PUSH=(5:1,8)


And

Code:
  REFORMAT FIELDS=(F1:1,4,9)



Hi,
I tried the code looking IBM Manuals as I want to understand what BEGIN and PUSH and learned about it. I too coded the same way what Rinsio had posted at first but I was giving push value as just 1 instead of that key in the 134th column that made wrong in the code.

My record length is 134 in two input files and output file.

One more doubt like if i give PUSH=(134:1,8) for the blank records in the BEGIN even then the records which are not blank will not have the key value in the 134th column right then how will those records will be included in the output file.
Back to top
View user's profile Send private message
Garry Carroll

Active Member


Joined: 08 May 2006
Posts: 988
Location: Dublin, Ireland / Edinburgh, Scotland

PostPosted: Thu Apr 23, 2015 8:31 pm    Post subject:
Reply with quote

Try it - the PUSH=(134:1,8) will put bytes 1 to 8 from the BEGIN= record into bytes 134-141 of every record until the next record with non-blank in 1,8. The BEGIN= record gets the PUSH value, so will be included in output.

Garry.
Back to top
View user's profile Send private message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 159
Location: India

PostPosted: Thu Apr 23, 2015 8:37 pm    Post subject:
Reply with quote

Garry Carroll wrote:
Try it - the PUSH=(134:1,8) will put bytes 1 to 8 from the BEGIN= record into bytes 134-141 of every record until the next record with non-blank in 1,8. The BEGIN= record gets the PUSH value, so will be included in output.

Garry.



Yeah ran it now understood how it is working. I misunderstood the concept first....Now its clear....thank you so much for explaining me the concepts. Learning a new concept today thanks a lot.
Back to top
View user's profile Send private message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 159
Location: India

PostPosted: Thu Apr 23, 2015 8:38 pm    Post subject:
Reply with quote

rinsio wrote:
Great Garry,
In the case the record lentgh is 80.

This is the code:

Code:

 //SYSIN    DD *                                               
     JOINKEYS F1=IN1,FIELDS=(73,8,A)                           
     JOINKEYS F2=IN2,FIELDS=(01,8,A)                           
     REFORMAT FIELDS=(F1:1,72)                                 
     OUTREC FIELDS=(1,72,8X)                                   
     OPTION COPY                                               
 /*                                                             
 //JNF1CNTL DD *                                               
     INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,8,CH,NE,C'        '),   
     PUSH=(73:1,8))                                             
 /*                                                             


Code:

 BROWSE    XXXXXX.MAINFR.SALIDA                       Line 00000000 Col 001 080
 Command ===>                                                  Scroll ===> CSR 
********************************* Top of Data **********************************
AAAAAAAA       BBBBBB       CCCCCC                                             
               B1B1B1                                                           
               B2B2B2                                                           
               B3B3B3                                                           
DDDDDDDD       EEEEEEE      FFFFFF                                             
                            F1F1F1F1                                           
******************************** Bottom of Data ********************************


Thanks
Regards



Thanks a lot Rinsio. I understood the concept behind the code now. Learned a concept today...Thank you....
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7225

PostPosted: Thu Apr 23, 2015 9:17 pm    Post subject: Reply to: Sort card to concatenate two records based on one condition
Reply with quote

Rinsio,

The data is shown as in order. So don't allow the default SORT to occur for each of the JOINKEYS files.

Extend outside the record. Then the entire formatting can be done in REFORMAT. With no need for INREC or OUTREC or OUTFIL BUILD.

See also here.


Code:
 //SYSIN    DD *                                               
     JOINKEYS F1=IN1,FIELDS=(81,8,A),SORTED,NOSEQCK                           
     JOINKEYS F2=IN2,FIELDS=(01,8,A),SORTED,NOSEQCK
     REFORMAT FIELDS=(F1:1,80)                                 
     OPTION COPY                                               
 /*                                                             
 //JNF1CNTL DD *                                               
     INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,8,CH,NE,C'        '),   
                        PUSH=(81:1,8))                                             
 /*
Back to top
View user's profile Send private message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 159
Location: India

PostPosted: Thu Apr 23, 2015 9:19 pm    Post subject:
Reply with quote

I have one more doubt like when I send this output file in excel format each record will be created as each row in the Excel right...? But what if I need it to be based on grouping like in excel.

All the Rows corresponding to AAAAAAA in one row with BBBBB, B1B1B1, B2B22B2 all in one column
and DDDDDDDin next row and EEEEE,E1E1E1 in one column of 2nd row.

will i be able to do that.....?
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Thu Apr 23, 2015 10:58 pm    Post subject: Reply to: Sort card to concatenate two records based on one condition
Reply with quote

Quote:
All the Rows corresponding to AAAAAAA in one row with BBBBB, B1B1B1, B2B22B2 all in one column

Do you mean BBBBB, B1B1B2, B2B2B2 under one column or inside one cell?

You can add a delimiter after every field so that they can be grouped under one column. May be a tab (X'05') or a comma (,)
Back to top
View user's profile Send private message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 159
Location: India

PostPosted: Thu Apr 23, 2015 11:28 pm    Post subject:
Reply with quote

One more doubt if I send mail of this final output file in comma separated format excel, the comma is added after each field for every row in the file. But actually what I need id the first roe grouped under AAAAAAAA should be in single row in excel. That is, the field values BBBBBB, B1B1B1,B2B2B3 all should in one cell column in that row. Is there any way to get like that....
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
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
This topic is locked: you cannot edit posts or make replies. How to pass the previous month date i... Suganya87 DFSORT/ICETOOL 5 Mon Oct 31, 2016 4:13 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm


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