Selecting Maximum Value

[makies]

Hello I have to sort the following data in order to geth the shop which has the biggest sales for each month for example. The first field is shop location, the second is the month and the third is total sales. Column leghts are 1,17 for locations, 19,2 for monts, 22,6 for sales

Code: Select all

london                  1         300
london                  2         150
milan                    1         310
milan                   3         120
Sacramento          2         100
Munich


I woul like display the shop with the biggest sales for each month

Expected output

Code: Select all

1    milan
2    london
3    milan


Thank you very much

[Frank Yaeger]

Here's a DFSORT/ICETOOL job that will do what you asked for:

Code: Select all

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN DD DSN=...  input file (FB)
//OUT DD DSN=...  output file (FB)
//TOOLIN  DD    *
SELECT FROM(IN) TO(OUT) ON(19,2,CH) FIRST USING(CTL1)
/*
//CTL1CNTL DD *
  SORT FIELDS=(19,2,CH,A,22,6,ZD,D)
  OUTFIL FNAMES=OUT,BUILD=(19,2,6:1,15)
/*


[makies]

Thanks for your time but can you explain how the code you 've written will select the maximum sales for each month??

[Frank Yaeger]

SORT FIELDS=(19,2,CH,A,22,6,ZD,D) sorts by date ascending and by sales descending, so the intermediate result for that would be:

Code: Select all

milan              1    310   
london             1    300   
london             2    150   
Sacramento         2    100   
milan              3    120   


SELECT ... ON(19,2,CH) FIRST selects the first record for each month, so that intermediate result would be:

Code: Select all

milan              1    310   
london             2    150   
milan              3    120   


Finally, the OUTFIL statement reformats the record with just the month and shop, so the final result would be:

Code: Select all

 1   milan     
 2   london     
 3   milan     


[makies]

Thank you very much that was really helpful, I really appreciate this man

[makies]

Code: Select all

//MYJOB    JOB USER=SUR0016,MSGCLASS=H,CLASS=A,NOTIFY=SUR0016
//S1       EXEC PGM=ICETOOL                                   
//TOOLMSG  DD SYSOUT=*                                       
//DFSMSG   DD SYSOUT=*                                       
//IN       DD DSN=SURMSTR.TRDATA2,DISP=SHR                   
//OUT      DD SYSOUT=*                                       
//TOOLIN   DD *                                               
  SELECT FROM(IN) TO(OUT) ON(24,2,ZD) FIRST USING(CTL1)       
/*                                                           
//CTL1CNTL DD *                                               
  SORT FIELDS=(24,2,ZD,A,27,7,ZD,D)                           
  OUTFIL FNAMES=OUT,BUILD=(24,2,7:1,16)                       
/*                                                           

i am getting an error MAXCC=16, does anyone know what's wrong with this one

[Frank Yaeger]

i am getting an error MAXCC=16, does anyone know what's wrong with this one

There's nothing obviously wrong with it and when I ran it here, I got RC=0. So something is different in what you're doing or in your shop.

The RC=16 indicates that DFSORT detected an error. It displays an error message to tell you what that error is. Look in the DFSMSG data set and you will see an error message of the form ICExxxA. You can look up that error message here:

http://publibz.boulder.ibm.com/cgi-bin/ ... 0721170811

If you can't figure out what it means from that, then post the DFSMSGS messages you received.

[makies]

where can you see the DFsMSG error ?? The error that i am getting after i submit the job is

21.22.37 JOB05827 $HASP165 MYJOB ENDED AT TSTMVS01 MAXCC=16 CN(INTERNAL)
***

[Frank Yaeger]

This is the same problem you're having in your other thread with not being able to see the SYSOUT messages.

You can write //TOOLMSG and //DFSMSG to permanent data sets so you can see the messages.

Change the DD statements to something like:

//TOOLMSG DD DSN=SURMSTR.TOOLSMG,DISP=(NEW,CATLG,DELETE),
// SPACE=(TRK,(5,5)),UNIT=SYSDA
//DFSMSG DD DSN=SURMSTR.DFSMSG,DISP=(NEW,CATLG,DELETE),
// SPACE=(TRK,(5,5)),UNIT=SYSDA

Then you should be able to see the messages in SURMSTR.TOOLMSG and SURMST.DFSMSG.

The next time you run, be sure to change //TOOLMSG and //DFSMSG to

//TOOLMSG DD DSN=SURMSTR.TOOLMSG,DISP=OLD
//DFSMSG DD DSN=SURMSTR.DFSMSG,DISP=OLD

since they are already allocated.