IBM Mainframe Forum

Good morning,

I transfer an issue if you could ye help me. Excuse me for my english

I have a stock of 14 million customers. These clients are organized into several groups and each group has an average balance. I need to group clients in the different groups, sort by average balance in ascending order, discard the first 10 customers each group (being ordered by descending balance rule customers more balance) and perform the summation of the average balance calculating 0.60%. Get two files of output of each group: one with the full stock of customers in each group and another file with the sum of the balance of each group (previously discarded 10 first records)

The file structure of input, sorted by client, is

customer pic x(10)
group 1 pic x(02)
group 2 pic x(02)
amount pic s9(13)v99 comp-3

The file structure of output1, output3, output5 is the same as the input

the file structure of output2, output4, output6 is

customer pic x(10)
amount pic s9(13)v99 comp-3


Example

Input
-------
0000000001 AA PP 5000
0000000003 AB PP 1000
0000000005 AC PP 3000
0000000006 AD PP 2500
0000000009 AA PP 8000
0000000012 AB PP 500
0000000015 AA PP 100
0000000016 AC PP 7000
0000000018 AD PP 1500
0000000020 AD PP 2000
0000000021 AC PP 9500
0000000029 AC PP 0600
0000000031 AF PP 0250
0000000041 AA PE 9300
0000000043 AB PE 1250
0000000045 AC PE 8000
0000000046 AD PE 9200
0000000049 AA PE 7000
0000000052 AB PE 7000
0000000055 AA PE 1500
0000000056 AC PE 9000
0000000058 AD PE 1200
0000000060 AD PE 950
0000000061 AC PE 1300
0000000069 AC PE 225
0000000071 AF PE 50
0000000081 SE AR 10300
0000000083 SE AR 12520
0000000082 SE AR 8000
0000000115 SE AR 4500
0000000125 SE AR 2434
0000000126 SE AR 8903
0000000155 SE AR 11000
0000000156 SE AR 5523
0000000158 SE AR 687
0000000163 SE AR 900
0000000167 SE AR 150
0000000168 SE AR 25
0000000200 SE AR 75


Output 1 (PP)
---------------
0000000021 AC PP 9500
0000000009 AA PP 8000
0000000016 AC PP 7000
0000000001 AA PP 5000
0000000005 AC PP 3000
0000000006 AD PP 2500
0000000020 AD PP 2000
0000000018 AD PP 1500
0000000003 AB PP 1000
0000000029 AC PP 600
0000000012 AB PP 500
0000000031 AF PP 250
0000000015 AA PP 100

Output 2 (PP)
---------------
0000000012 4335 (500+250+100 * 0,60%) --> (customer is not important. amount yes)

Output 3 (PE)
---------------
0000000041 AA PE 9300
0000000056 AC PE 9000
0000000046 AD PE 9200
0000000045 AC PE 8000
0000000049 AA PE 7000
0000000052 AB PE 7000
0000000055 AA PE 1500
0000000061 AC PE 1300
0000000043 AB PE 1250
0000000058 AD PE 1200
0000000060 AD PE 950
0000000069 AC PE 225
0000000071 AF PE 50

Output 4 (PE)
---------------
0000000060 9003,75 (950+225+50 * 0,60%) --> (customer is not important. amount yes)

Output 5 (SE, AR)
---------------------
0000000083 SE AR 12520
0000000155 SE AR 11000
0000000081 SE AR 10300
0000000126 SE AR 8903
0000000082 SE AR 8000
0000000156 SE AR 5523
0000000115 SE AR 4500
0000000125 SE AR 2434
0000000163 SE AR 900
0000000158 SE AR 687
0000000167 SE AR 150
0000000200 SE AR 75
0000000168 SE AR 25

Output 6 (SE, AR)
---------------------
0000000167 375 (150+75+25 * 0,60%) --> (customer is not important. amount yes)

I have made the next step does not work me. I do not get to do the skiprec and then summation. Can you help? Because I need to do multiple filters, we would appreciate having them in one step, if possible, of course

//SORT030 EXEC PGM=ICETOOL,REGION=8M,COND=(0,NE)
//DFSPARM DD *
OPTION DYNALLOC=(SYSALLDA,32)
/*
//TOOLIN DD *
SORT FROM(SORTIN) TO(SORTOUT1) USING(SAL1)
SORT FROM(SORTIN) TO(SORTOUT2) USING(SAL2)
SORT FROM(SORTIN) TO(SORTOUT3) USING(SAL3)
SORT FROM(SORTIN) TO(SORTOUT4) USING(SAL4)
SORT FROM(SORTIN) TO(SORTOUT3) USING(SAL5)
SORT FROM(SORTIN) TO(SORTOUT4) USING(SAL6)
/*
//SORTIN DD DSN=&&dsn,DISP=SHR
//SORTOUT1 DD DSN=OUTPUT1,
// DISP=(,CATLG,DELETE),
// SPACE=(TRK,(100,050),RLSE)
//SORTOUT2 DD DSN=OUTPUT2,
// DISP=(,CATLG,DELETE),
// SPACE=(TRK,(100,050),RLSE)
//SORTOUT3 DD DSN=OUTPUT3,
// DISP=(,CATLG,DELETE),
// SPACE=(TRK,(100,050),RLSE)
//SORTOUT4 DD DSN=OUTPUT4,
// DISP=(,CATLG,DELETE),
// SPACE=(TRK,(100,050),RLSE)
//SORTOUT5 DD DSN=OUTPUT5,
// DISP=(,CATLG,DELETE),
// SPACE=(TRK,(100,050),RLSE)
//SORTOUT6 DD DSN=OUTPUT6,
// DISP=(,CATLG,DELETE),
// SPACE=(TRK,(100,050),RLSE)
//*
//SAL1CNTL DD *
SORT FIELDS=COPY
INCLUDE COND=(13,2,CH,EQ,C'PP')
/*
//SAL2CNTL DD *
SORT FIELDS=(13,2,CH,A),SKIPREC=10
INCLUDE COND=(13,2,CH,EQ,C'PP')
SUM FIELDS=(15,8,PD)
OUTREC FIELDS=(15,8,((15,8,PD,MUL,+60),DIV,+100),TO=PD,LENGTH=8)
/*
//SAL3CNTL DD *
SORT FIELDS=COPY
INCLUDE COND=(13,2,CH,EQ,C'PE')
/*
//SAL4CNTL DD *
SORT FIELDS=(13,2,CH,A),SKIPREC=10
INCLUDE COND=(13,2,CH,EQ,C'PE')
SUM FIELDS=(15,8,PD)
OUTREC FIELDS=(15,8,((15,8,PD,MUL,+60),DIV,+100),TO=PD,LENGTH=8)
/*
//SAL5CNTL DD *
SORT FIELDS=COPY
INCLUDE COND=(11,2,CH,EQ,C'SE',AND,13,2,CH,EQ,C'AR')
/*
//SAL6CNTL DD *
SORT FIELDS=(13,2,CH,A),SKIPREC=10
INCLUDE COND=(11,2,CH,EQ,C'SE',AND,13,2,CH,EQ,C'AR')
SUM FIELDS=(15,8,PD)
OUTREC FIELDS=(15,8,((15,8,PD,MUL,+60),DIV,+100),TO=PD,LENGTH=8)
/*
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=A
//SYSIN DD *
/*

Thanks a lot of!!!

SKIPREC=10 will ignore (delete) the first 10 records of the input file from further processing.

Excuse me BillyBoyo
maybe I did not explain properly...
The input file is order by customer. I want obtain 6 output files.
Output1 --> customer group by "PP" and ordered by descending amount
Output2 --> customer group by "PP" ordered by descending amount and delete the first 10 customer
Output3 --> customer group by "PE" and ordered by amount
Output4 --> customer group by "PE" ordered by descending amount and delete the first 10 customer
Output5 --> customer group by "SE" and "AR" and ordered by amount
Output6 --> customer group by "SE" and "AR ordered by descending amount and delete the first 10 customer

If I execute Output1, Output3 and Output5 I don't have problem. The output is Ok
If I execute Output2, Output4 and Output6 I have problem. The Output is not OK. Delete the first 10 customer regardless balance but I need that the first 10 deleted with higher balance of echar group and after make the sum

I need to sort ascending by group (sometimes sort by Group 1 and Group 2 other times) and amount in descending order later, delete the first 10 records and make the sum with the remaining records.

if you saw my example you'll see what I want to do: sort descending records for amount field (the largest amounts go first), delete ten records (with largest amount) of each group (PP, PE, and SE-AR) and make the sum with the remaining records). But if I executed this, not working correctly. I don't know if I cant order ascending by a fields, order descending amount, skiprec ten records (the higher amount) and sum amount remainded.