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 ROW in to multiple column

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
sreekumarh

New User


Joined: 08 Jul 2015
Posts: 1
Location: india

PostPosted: Wed Jul 08, 2015 4:10 am    Post subject: Sort ROW in to multiple column
Reply with quote

I am trying to use ICETOOL /DFSORT

Input :

Code:
XPRMAA
BPRMAB
CPRMAC
DPRMAD
EPRMAE
FPRMAF
GPRMA0
HPRMA1
IPRMA2
JPRMA3
KPRMA4
LPRMA5
MPRMA6
NPRMA7
OPRMA7
PPRMA7
QPRMA7
RPRMA7
SPRMA7
TPRMA7
UPRMA7


Output :

Code:
XPRMAA   HPRMA1  OPRMA7     
BPRMAB   IPRMA2  PPRMA7     
CPRMAC  JPRMA3  QPRMA7     
DPRMAD  KPRMA4  RPRMA7     
EPRMAE   LPRMA5  SPRMA7     
FPRMAF   MPRMA6  TPRMA7     
GPRMA0  NPRMA7  UPRMA


Code'd
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Wed Jul 08, 2015 10:59 am    Post subject: Reply to: Sort ROW in to multiple column
Reply with quote

for data and <code> snippets good manners suggest to use the code tags
( the text is displayed with a fixed font and make easier for the people spending their OWN time helping to understand what You are trying to tell )

Code:

XPRMAA
BPRMAB
CPRMAC
DPRMAD
EPRMAE
FPRMAF
GPRMA0
HPRMA1
IPRMA2
JPRMA3
KPRMA4
LPRMA5
MPRMA6
NPRMA7
OPRMA7
PPRMA7
QPRMA7
RPRMA7
SPRMA7
TPRMA7
UPRMA7


Code:

XPRMAA HPRMA1 OPRMA7
BPRMAB IPRMA2 PPRMA7
CPRMAC JPRMA3 QPRMA7
DPRMAD KPRMA4 RPRMA7
EPRMAE LPRMA5 SPRMA7
FPRMAF MPRMA6 TPRMA7
GPRMA0 NPRMA7 UPRMA7


unfortunately the way the output is formatted You will need at least two steps
the first one to decide where to split the data
and the second for the reformatting

Code:
1
2
. split point
3
4
.split point
5
6


Code:
1 3 5
2 4 6


it would have been easier for an horizontal reformat

sort can do it in a single pass ( or ICETOOL RESIZE )

Code:
1
2
3
4
5
6

Code:
1 2 3
4 5 6
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 158
Location: Chennai

PostPosted: Wed Jul 08, 2015 12:10 pm    Post subject: Reply to: Sort ROW in to multiple column
Reply with quote

You always have 21 records as a input ?, if yes, then use the below code, if no shows us the sample input and required output for more than 21 records.
Code:

//STEP01 EXEC PGM=SORT               
//SORTIN DD *                       
XPRMAA                               
BPRMAB                               
CPRMAC                               
DPRMAD                               
EPRMAE                               
FPRMAF                               
GPRMA0                               
HPRMA1                               
IPRMA2                               
JPRMA3                               
KPRMA4                               
LPRMA5                               
MPRMA6                               
NPRMA7                               
OPRMA7                               
PPRMA7   
QPRMA7               
RPRMA7               
SPRMA7               
TPRMA7               
UPRMA7               
//SYSOUT DD SYSOUT=* 
//SORTOUT DD SYSOUT=*                           
  INREC IFTHEN=(WHEN=GROUP,RECORDS=7,PUSH=(81:ID=1)),             
        IFTHEN=(WHEN=GROUP,KEYBEGIN=(81,1),PUSH=(82:SEQ=1))       
  SORT FIELDS=(82,1,CH,A)                                         
  OUTREC IFTHEN=(WHEN=GROUP,BEGIN=(81,1,CH,EQ,C'1'),RECORDS=3,   
                 PUSH=(7:1,6)),                                   
         IFTHEN=(WHEN=GROUP,BEGIN=(81,1,CH,EQ,C'2'),RECORDS=2,   
                 PUSH=(14:1,6)),                                 
         IFTHEN=(WHEN=GROUP,BEGIN=(81,1,CH,EQ,C'3'),RECORDS=1,   
                 PUSH=(21:1,6))                                   
  OUTFIL REMOVECC,NODETAIL,BUILD=(80X),                           
         SECTIONS=(82,1,TRAILER3=(7,29))                           
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Wed Jul 08, 2015 1:09 pm    Post subject: Reply to: Sort ROW in to multiple column
Reply with quote

The first two GROUPs can be merged together.

The fifth group, with RECORDS=1, can be turned into a WHEN=(logicalexpression).

You are relying on EQUALS for your SORT to work as expected. If you put on a second key of the 81,1 you don't need EQUALS.

An alternative for the OUTFIL is to use INCLUDE= for 81,1 equal to 3 and just have an ordinary OUTFIL otherwise (cutting down to 80, only outputting the data required). I'm not sure 29 is the correct length (three sixes and two spacing characters gets me to 20).

Can also be done with a USING on RESIZE for simplified code (as RESIZE would do the sticking-together).
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 158
Location: Chennai

PostPosted: Wed Jul 08, 2015 10:06 pm    Post subject: Reply to: Sort ROW in to multiple column
Reply with quote

Bill,

Is this what you mean

Code:

INREC IFTHEN=(WHEN=GROUP,RECORDS=7,PUSH=(81:ID=1)),               
      IFTHEN=(WHEN=GROUP,KEYBEGIN=(81,1),PUSH=(82:SEQ=1))         
SORT FIELDS=(82,1,CH,A,81,1,CH,A)                                 
OUTREC IFTHEN=(WHEN=GROUP,BEGIN=(81,1,CH,EQ,C'1'),RECORDS=3,     
               PUSH=(7:1,6)),                                     
       IFTHEN=(WHEN=GROUP,BEGIN=(81,1,CH,EQ,C'2'),RECORDS=2,     
               PUSH=(14:1,6)),                                   
       IFTHEN=(WHEN=GROUP,BEGIN=(81,1,CH,EQ,C'3'),RECORDS=1,     
               PUSH=(21:1,6))                                     
OUTFIL INCLUDE=(81,1,CH,EQ,C'3'),BUILD=(7,20,80:X)               


Bill Woodger wrote:
The first two GROUPs can be merged together.


I dont know how to do this, Please help.

Bill woodger wrote:
The fifth group, with RECORDS=1, can be turned into a WHEN=(logicalexpression).


Why we should change this to WHEN=(logicalexpression), Is it for performance ?

Bill woodger wrote:

You are relying on EQUALS for your SORT to work as expected. If you put on a second key of the 81,1 you don't need EQUALS.


You mean this ? Please correct me if i amwrong
Code:

SORT FIELDS=(82,1,CH,A,81,1,CH,A)


Bill woodger wrote:

An alternative for the OUTFIL is to use INCLUDE= for 81,1 equal to 3 and just have an ordinary OUTFIL otherwise (cutting down to 80, only outputting the data required). I'm not sure 29 is the correct length (three sixes and two spacing characters gets me to 20).

You are right, changed it.

Quote:

Can also be done with a USING on RESIZE for simplified code (as RESIZE would do the sticking-together).


The reason why i didnt choose this because, we need Records from 1st,7th,14 etc. and not 1st, 2nd, 3rd.

Thanks for the advise.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Wed Jul 08, 2015 11:15 pm    Post subject: Reply to: Sort ROW in to multiple column
Reply with quote

Here's a cut-down version of yours, with positions amended for easy viewing:

Code:
 OPTION COPY
 INREC IFTHEN=(WHEN=GROUP,RECORDS=7,PUSH=(10:ID=1)),
        IFTHEN=(WHEN=GROUP,KEYBEGIN=(10,1),PUSH=(11:SEQ=1))


Here's the consolidation of the two WHEN=GROUPs:

Code:
 OPTION COPY
 INREC IFTHEN=(WHEN=GROUP,RECORDS=7,PUSH=(10:ID=1,SEQ=1))


The output, from both, is:

Code:
XPRMAA   11
BPRMAB   12
CPRMAC   13
DPRMAD   14
EPRMAE   15
FPRMAF   16
GPRMA0   17
HPRMA1   21
IPRMA2   22
JPRMA3   23
KPRMA4   24
LPRMA5   25
MPRMA6   26
NPRMA7   27
OPRMA7   31
PPRMA7   32
QPRMA7   33
RPRMA7   34
SPRMA7   35
TPRMA7   36
UPRMA7   37


The reason is you are effectively specifying the same group, in two different ways. Firstly by RECORDS=7, setting the ID, then by change of ID.

One GROUP is sufficient, specifying both the ID and the SEQ on the PUSH.

The GROUP with RECORDS=1 is a rare beast. It only affects the current record. You may find a use for it when it is followed by another WHEN=GROUP, but if there is no subsequent WHEN=GROUP it can be replaced with a simple WHEN=(logicalexpression) which indeed will be more efficient (not that you'd notice with 21 records).

Yes, if you SORT on the SEQ and then on the ID there is no danger (without EQUALS) of getting the columns mixed.

ICETOOL's RESIZE, as with other data-manipulation operators, operates at the OUTREC stage (which is why you can't specify OUTREC in the USING control cards). This means that if the data is in the wrong order for the RESIZE, it can be formatted with INREC (if necessary) and SORTed before the RESIZE is done, and OUTFIL can even be used for further processing.

Of course, if it is not a fixed number of records, it will require two passes of the data. It would be possible to generate the control cards for the second pass.
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 158
Location: Chennai

PostPosted: Wed Jul 08, 2015 11:36 pm    Post subject: Reply to: Sort ROW in to multiple column
Reply with quote

Thanks for the explanation, Modified the code as per your advise.

Code:

INREC IFTHEN=(WHEN=GROUP,RECORDS=7,PUSH=(81:ID=1,SEQ=1))
SORT FIELDS=(82,1,CH,A,81,1,CH,A)                                 
OUTREC IFTHEN=(WHEN=GROUP,BEGIN=(81,1,CH,EQ,C'1'),RECORDS=3,     
               PUSH=(7:1,6)),                                     
       IFTHEN=(WHEN=GROUP,BEGIN=(81,1,CH,EQ,C'2'),RECORDS=2,     
               PUSH=(14:1,6)),                                   
       IFTHEN=(WHEN=(81,1,CH,EQ,C'3'),OVERLAY=(21:1,6))
OUTFIL INCLUDE=(81,1,CH,EQ,C'3'),BUILD=(7,20,80:X)     
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 158
Location: Chennai

PostPosted: Thu Jul 09, 2015 10:49 am    Post subject: Reply to: Sort ROW in to multiple column
Reply with quote

if More than 21 records

Code:

                                           
INREC IFTHEN=(WHEN=GROUP,RECORDS=7,PUSH=(81:ID=1,SEQ=1))       
SORT FIELDS=(82,1,CH,A,81,1,CH,A)                               
OUTREC IFTHEN=(WHEN=GROUP,RECORDS=3,PUSH=(83:SEQ=1)),           
       IFTHEN=(WHEN=GROUP,BEGIN=(83,1,CH,EQ,C'1'),RECORDS=3,   
               PUSH=(7:1,6)),                                   
       IFTHEN=(WHEN=GROUP,BEGIN=(83,1,CH,EQ,C'2'),RECORDS=2,   
               PUSH=(14:1,6)),                                 
       IFTHEN=(WHEN=(83,1,CH,EQ,C'3'),OVERLAY=(21:1,6))         
OUTFIL INCLUDE=(83,1,CH,EQ,C'3'),BUILD=(7,20,80:X)             


But still sreekumarh has to confirm the output, how it should be, if it is greater than 21 records.
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 158
Location: Chennai

PostPosted: Thu Jul 09, 2015 11:03 am    Post subject: Reply to: Sort ROW in to multiple column
Reply with quote

Moderators, Please edit my previous code with this, Because previous code will work only for multiple of 3 records. below solution will work irrespective of that.

Code:

OUTFIL FNAMES=OUT1                                           
INREC IFTHEN=(WHEN=GROUP,RECORDS=7,PUSH=(81:ID=1,SEQ=1))     
SORT FIELDS=(82,1,CH,A,81,1,CH,A)                           
OUTREC IFTHEN=(WHEN=GROUP,RECORDS=3,PUSH=(81:SEQ=1,ID=1)),   
       IFTHEN=(WHEN=GROUP,BEGIN=(81,1,CH,EQ,C'1'),RECORDS=3,
               PUSH=(7:1,6)),                               
       IFTHEN=(WHEN=GROUP,BEGIN=(81,1,CH,EQ,C'2'),RECORDS=2,
               PUSH=(14:1,6)),                               
       IFTHEN=(WHEN=(81,1,CH,EQ,C'3'),OVERLAY=(21:1,6))     
   OUTFIL REMOVECC,NODETAIL,BUILD=(80X),                     
          SECTIONS=(82,1,TRAILER3=(7,20))                   
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 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
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm
No new posts How to delete second instance from Fl... Gunapala CN DFSORT/ICETOOL 6 Tue Oct 18, 2016 11:42 pm


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