Page 1 of 1

Maxvalue of a Group

PostPosted: Fri May 18, 2012 11:48 pm
by ibmmf4u
Hi Everyone,

My requirement goes this way, I would like to find out the maximum value of a particular group and write it to an output file. Mentioned below is an example.

Input file:-
TEST1    0010
TEST1    0020
TEST2    0030
TEST2    0040
TEST3    0050
TEST3    0060
TEST3    0070
TEST4    0080
TEST4    0060
TEST5    0090
TEST5    0100



Output file:-
TEST1    0020
TEST2    0040
TEST3    0070
TEST4    0080
TEST5    0100


Can someone provide me the sort card in achieving the above

Thanks in advance!!

Re: Maxvalue of a Group

PostPosted: Sat May 19, 2012 11:38 am
by ibmmf4u
Hi All,

I can able to achieve the above with synctool. Pasted below is the code that's working fine.

//STEP010    EXEC  PGM=SYNCTOOL                                 
//TOOLMSG DD SYSOUT=*                                     
//SSMSG      DD SYSOUT=*                                     
//INPUT       DD  *                                             
TEST1    0010                                             
TEST1    0020                                             
TEST2    0030                                             
TEST2    0040                                             
TEST3    0050                                             
TEST3    0060                                             
TEST3    0070                                             
TEST4    0080                                             
TEST4    0060                                             
TEST5    0090                                             
TEST5    0100                                             
//OUTPUT DD SYSOUT=*                                       
//TOOLIN  DD    *                                         
SELECT FROM(INPUT) TO(OUTPUT) ON(1,5,CH) FIRST USING(CTL1)
/*                                                         
//CTL1CNTL DD *                   
  SORT FIELDS=(1,5,CH,A,10,4,ZD,D)
/*                                 


Its giving me the exact output i.e. required.

However earlier, i am able to sort the records in the order i.e.required as per the output with the below sort card

SORT FIELDS=(1,5,CH,A,10,4,ZD,D)


But can't able to eliminate the duplicates as per the required output. Pasted below is the output file that was generated with the help of the above sort card.

TEST1    0020
TEST1    0010
TEST2    0040
TEST2    0030
TEST3    0070
TEST3    0060
TEST3    0050
TEST4    0080
TEST4    0060
TEST5    0100
TEST5    0090


Can someone provide me the sort card on how to eliminate the duplicates from the above the file and get the required output.

Thanks in advance!!!

Re: Maxvalue of a Group

PostPosted: Sat May 19, 2012 8:04 pm
by dick scherrer
Good to hear it is working - thank you for letting us know :)

d

Re: Maxvalue of a Group

PostPosted: Sat May 19, 2012 9:52 pm
by ibmmf4u
Thank you Dick!!

Can you please let me know ho w to eliminate the duplicates from the below intermediate file .

    TEST1    0020
    TEST1    0010
    TEST2    0040
    TEST2    0030
    TEST3    0070
    TEST3    0060
    TEST3    0050
    TEST4    0080
    TEST4    0060
    TEST5    0100
    TEST5    0090



Thanks in advance

Re: Maxvalue of a Group

PostPosted: Sun May 20, 2012 2:37 pm
by ibmmf4u
Hi All,

I can able to achieve it with help of sort. pasted below is the code that's working fine.

//STEP010  EXEC PGM=SORT
//SORTIN   DD *         
    TEST1    0010       
    TEST1    0020       
    TEST2    0030       
    TEST2    0040       
    TEST3    0050       
    TEST3    0060       
    TEST3    0070       
    TEST4    0080       
    TEST4    0060       
    TEST5    0090       
    TEST5    0100                                   
//SORTOUT  DD DSN=TEST.SORT.RES,DISP=SHR 
//SYSOUT   DD SYSOUT=*                             
//SYSIN    DD *                                     
  SORT FIELDS=(5,5,CH,A,14,4,ZD,D)                 
/*                                                 
//STEP020  EXEC PGM=SORT                           
//SYSOUT   DD  SYSOUT=*                             
//SORTIN   DD  DSN=TEST.SORT.RES,DISP=SHR
//SORTOUT  DD  SYSOUT=*                             
//SYSIN    DD  *                                   
  SORT FIELDS=(5,5,CH,A)                           
  SUM FIELDS=NONE                                   
/*                                                 

Re: Maxvalue of a Group

PostPosted: Sun May 20, 2012 10:19 pm
by BillyBoyo
OK, you might also want to see if your release supports WHEN=GROUP.

If, so, you should be OK with one step.

Do the sort (from your first step, with the A/D depending on whether you want to retain lowest/highest amount), then with OUTREC (which is processed after SORT/SUM) use WHEN=GROUP to set a sequence number.

With OUTFIL, INCLUDE only those records with sequence number of one, then return record to desired length (with BUILD, for instance).

Re: Maxvalue of a Group

PostPosted: Sun May 20, 2012 10:36 pm
by dick scherrer
Hello,

I didn't completely comprehend what i replied to last time. My bad :oops:

Do try what BB has suggested and try to have only one step. It there only 100 records, 2 steps might not be so bad, but if there are 100million records. . .

Re: Maxvalue of a Group

PostPosted: Sun May 20, 2012 11:22 pm
by ibmmf4u
Thanks Bill & Dick.

I shall try the same and let you know the outcome.

Re: Maxvalue of a Group

PostPosted: Mon May 21, 2012 3:21 pm
by bodatrinadh
Hello ibmmf4u,

If you are using SYNCSORT FOR Z/OS 1.4.0.1R version, you can try below code.

//STEP1    EXEC PGM=SORT   
//SYSPRINT DD SYSOUT=*     
//SYSOUT DD SYSOUT=*       
//SORTIN   DD *           
TEST1    0020             
TEST2    0030             
TEST2    0040             
TEST3    0050             
TEST3    0060             
TEST3    0070             
TEST4    0080             
TEST4    0060             
TEST5    0090             
TEST5    0100             
//SORTOUT DD SYSOUT=*     
//SYSIN DD *               
  SORT FIELDS=(1,5,CH,A)   
  DUPKEYS MAX=(10,4,ZD)   


Output:-

TEST1    0020
TEST2    0040
TEST3    0070
TEST4    0080
TEST5    0100


Thanks
-3nadh

Re: Maxvalue of a Group

PostPosted: Mon May 21, 2012 9:49 pm
by ibmmf4u
Hi 3nadh,

Thanks a lot. we are currently using syncsort 1.3.2.2R. Its working fine here.

Regards,