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

SORT HELP - SORT A COLUMN and GROUP BASED ON KEY


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

New User


Joined: 14 May 2007
Posts: 7
Location: CT, USA

PostPosted: Thu Aug 05, 2021 10:46 pm
Reply with quote

Hello Team,

I have an input file as follows.

KEY(Pos 1-8) %AMOUNT (Pos 10-16)
TABLE1 88.92
TABLE2 87.00
TABLE1 86.70
TABLE1 86.00
TABLE3 85.59
TABLE4 85.00
TABLE2 84.92
TABLE1 80.00
TABLE6 90.00

I want to SORT the file first based on highest% value and for every highest % value of a table all other records for that table should be sorted in the order.

Output expected

TABLE6 90.00
TABLE1 88.92
TABLE1 86.70
TABLE1 86.00
TABLE1 80.00
TABLE2 87.00
TABLE2 84.92
TABLE3 85.59
TABLE4 85.00

I have tried several options but not able to find a way to do this.

Option-1 I tried
//SYSIN DD *
SORT FIELDS=(10,6,CH,D)
OUTREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,8),
PUSH=(1:1,20))
/*

Option-2 I tried
SORT FIELDS=(1,8,CH,A,10,6,CH,D)
OUTREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,8),
PUSH=(1:1,20))

Option-3 I tried
//SYSIN DD *
SORT FIELDS=(10,6,ZD,A,1,8,CH,A)
OUTREC FIELDS=(1,20)
/*

I have been searching all the forums and have not had any luck so far. Any help would be greatly appreciated.
Back to top
View user's profile Send private message
sergeyken
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1189

PostPosted: Fri Aug 06, 2021 1:37 am
Reply with quote

Example #1 from ANY(!) Sort utility manual.

Code:
 SORT FIELDS=(1,8,CH,A,
             10,16,SFF,D)
 END

Code:
 SORT FIELDS=(10,16,SFF,D,
              1,8,CH,A)
 END

Format CH may not always work for numeric data, but sometimes it does.

P.S.
The general idea of your desired output is not fully clear…
Back to top
View user's profile Send private message
hellosri

New User


Joined: 14 May 2007
Posts: 7
Location: CT, USA

PostPosted: Fri Aug 06, 2021 2:31 am
Reply with quote

Thank you for your suggesstion but it didnt work and I apologize for not being more cleared. Let me try one more time to see if you can understand.


KEY(Pos 1-8) %AMOUNT (Pos 10-16)
TABLE1 88.92 -> First highest value for TABLE1, but this is second highest value of all records.
TABLE2 87.00 -> First highest value for TABLE2, but this is the third highest value of all records.
TABLE1 86.70
TABLE1 86.00
TABLE3 85.59 -> First highest value for TABLE3
TABLE4 85.00 -> First highest value for TABLE4
TABLE2 84.92
TABLE1 80.00
TABLE6 90.00 -> First highest value for TABLE6, but this is the first highest value of all records.


The output file should have TABLE6 first because 90.00 is the highest % of all the records. And if there are more records for the same TABLE6 it should be followed. In the above example there is only one TABLE6. This completes the first highest % row written and all the records associated for that TABLE6. The next highest will be for TABLE1 88.92 and all the records for TABLE1 should be sorted in the descending order of the % and so on for other tables also. I know its little complicated but my final output should be first sorted based on % value in descending order and write all the records for that associated table and go on. Let me know if this helps.

Output expected

TABLE6 90.00
TABLE1 88.92
TABLE1 86.70
TABLE1 86.00
TABLE1 80.00
TABLE2 87.00
TABLE2 84.92
TABLE3 85.59
TABLE4 85.00
Back to top
View user's profile Send private message
Joerg.Findeisen

Active Member


Joined: 15 Aug 2015
Posts: 607
Location: Bamberg, Germany

PostPosted: Fri Aug 06, 2021 10:19 am
Reply with quote

I am afraid you have to take multiple passes to achieve what you want.
Back to top
View user's profile Send private message
sergeyken
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1189

PostPosted: Fri Aug 06, 2021 5:48 pm
Reply with quote

Those are two different goals:
1) get one single line with the highest numeric value (whatever the value means)
2) re-order all rows of the table in the required order

It is just senseless idea to mix those two different goals, different methods, different results, and different statements into one single task/code/resulting table.

They are two completely separate tasks, and two completely different output results

Try to perform those two tasks separately, and do not mix them into one messy task.

THEY ARE COMPLETELY DIFFERENT, do have nothing in common (except the same input data)

P.S.
Again, as in >90% of all the questions at this forum, the issue is not a specific programming tool, or any language syntax, or an internal function, but just inability of abstract logical thinking in general, and lack of experience in algorithm design
Back to top
View user's profile Send private message
hellosri

New User


Joined: 14 May 2007
Posts: 7
Location: CT, USA

PostPosted: Fri Aug 06, 2021 8:18 pm
Reply with quote

Thanks, it is okay for people to think and try to come up to achieve solutions in a single step. Atleast I tried and that is why I requested help to see if there were any feasible solution. Thank you for reviewing and trying though.

Cheers Mate!
Back to top
View user's profile Send private message
Joerg.Findeisen

Active Member


Joined: 15 Aug 2015
Posts: 607
Location: Bamberg, Germany

PostPosted: Sat Aug 07, 2021 5:05 am
Reply with quote

Code:
//PREPARE  EXEC PGM=ICEMAN
//SORTIN   DD *                                                   
TABLE1 88.92                                                       
TABLE2 87.00                                                       
TABLE1 86.70                                                       
TABLE1 86.00                                                       
TABLE3 85.59                                                       
TABLE4 85.00                                                       
TABLE2 84.92                                                       
TABLE1 80.00                                                       
TABLE6 90.00                                                       
/*                                                                 
//SYSOUT   DD SYSOUT=*                                             
//F1       DD DISP=(NEW,PASS),UNIT=SYSALLDA,SPACE=(CYL,(2,1),RLSE)
//F2       DD DISP=(NEW,PASS),UNIT=SYSALLDA,SPACE=(CYL,(2,1),RLSE)
//SORTOUT  DD DUMMY
//SYSIN    DD *                                                   
  SORT FIELDS=(1,6,CH,A,8,5,SFF,D)                                 
  OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(20:SEQNUM,4,ZD,RESTART=(1,6)))
  OUTFIL FNAMES=(F1),                                             
    REMOVECC                                                       
  OUTFIL FNAMES=(F2),                                             
    INCLUDE=(20,4,ZD,EQ,+1),                                       
    REMOVECC,                                                     
    BUILD=(1,12)                                                   
  END                                                             
/*                                                                 
//WHATEVER EXEC PGM=ICEMAN
//F1       DD DISP=(OLD,PASS),DSN=*.PREPARE.F1                     
//F2       DD DISP=(OLD,PASS),DSN=*.PREPARE.F2                     
//SYSOUT   DD SYSOUT=*                                             
//SORTOUT  DD SYSOUT=*                                             
//SYSIN    DD *                                                   
  JOINKEYS F1=F1,FIELDS=(1,6,D)               
  JOINKEYS F2=F2,FIELDS=(1,6,D)               
  REFORMAT FIELDS=(F1:1,24,F2:8,5)             
  SORT FIELDS=(25,5,SFF,D,1,6,CH,A,20,4,ZD,A) 
  OUTFIL FNAMES=(SORTOUT),                     
    REMOVECC,                                 
    BUILD=(1,12)                               
  END                                         
/*

Output:
Code:
****** *****************
000001 TABLE6 90.00     
000002 TABLE1 88.92     
000003 TABLE1 86.70     
000004 TABLE1 86.00     
000005 TABLE1 80.00     
000006 TABLE2 87.00     
000007 TABLE2 84.92     
000008 TABLE3 85.59     
000009 TABLE4 85.00     
****** *****************
Back to top
View user's profile Send private message
hellosri

New User


Joined: 14 May 2007
Posts: 7
Location: CT, USA

PostPosted: Sat Aug 07, 2021 6:38 am
Reply with quote

Joerg, Thank you for your response. I am out of office next week. But I will try your code as soon as I come back and will respond.
Thank you for taking time to help me achieve with the solution although it is in two steps.

Thanks
Sri
Back to top
View user's profile Send private message
sergeyken
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1189

PostPosted: Sat Aug 07, 2021 8:39 am
Reply with quote

Using ICETOOL/SYNCTOOL you can “pack” these two JCL steps into two passes within a single JCL EXEC PGM
Back to top
View user's profile Send private message
hellosri

New User


Joined: 14 May 2007
Posts: 7
Location: CT, USA

PostPosted: Mon Aug 16, 2021 10:23 pm
Reply with quote

Joerg - I tried out the solution you gave and it worked as expected. Thank you once again for helping out and for your time. Very much appreciated.
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
This topic is locked: you cannot edit posts or make replies. Sort to include records of file 2 int... Java & MQSeries 1
No new posts Converting unpacked fields to pack us... SYNCSORT 4
No new posts SORT for dynamic trailer record + CSV... DFSORT/ICETOOL 14
No new posts Sort w/OUTREC Question DFSORT/ICETOOL 2
This topic is locked: you cannot edit posts or make replies. SUM based on two conditions using SYN... SYNCSORT 7
Search our Forums:

Back to Top