SORT Card to sum the values of same-keys records



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

SORT Card to sum the values of same-keys records

Postby kpargao » Tue Mar 08, 2011 2:31 pm

Hi,

I have below requirement. My file is:
EMP-ID;JULIANDATE;XXXXXX;

Now I want to sum up the records where emp-id and julian date matches and to keep only the first record in output with the count.

Example:
EB111111111;2011068;xxxxxx,yyyyyy,zzzzz,dddddd ------>FIRST RECORD
EB111111111;2011068;xxxxxx,yyyyyy,zzzzz,dddddd ------>SECOND RECORD
EB111111111;2011069;xxxxxx,yyyyyy,zzzzz,dddddd ------->THIRD RECORD
EB122222222;2011068;xxxxxx,yyyyyy,zzzzz,dddddd ------->FOURTH RECORD

THE OUTPUT FILE SHOULD BE:

EB111111111;2011068;xxxxxx,yyyyyy,zzzzz,dddddd,02 ------>FIRST RECORD
EB111111111;2011069;xxxxxx,yyyyyy,zzzzz,dddddd,01 ------->THIRD RECORD
EB122222222;2011068;xxxxxx,yyyyyy,zzzzz,dddddd,01 ------->FOURTH RECORD

I TRIED THE BELOW SORT:

SORT FIELDS=(1,11,CH,A,13,7,CH,A),EQUALS
SUM FIELDS=(70,2,PD)

BUT I AM GETTING SOC7 I ALSO TRIED SUM FIELDS=(70,2,BI) BUT GETTING RC=04 AND IMPROPER RESULTS WITH DATA OVERFLOW ISSUE.

CAN ANYONE PLEASE SUGGEST HOW TO RESOLVE AND GET THE OUTPUT AS NEEDED.
kpargao
 
Posts: 2
Joined: Tue Mar 08, 2011 1:35 pm
Has thanked: 0 time
Been thanked: 0 time

Re: SORT Card to sum the values of same-keys records

 

Re: SORT Card to sum the values of same-keys records

Postby kpargao » Tue Mar 08, 2011 4:10 pm

Hi,

In order to get the desired result I did the following. I am creating my input file by using OUTREC so when I am doing OUTREC I did the following:

SORT FIELDS=COPY
OUTREC FIELDS=(1:1,12,13:24,56,69:1C';',70:1C'1',71:10X)
END

I am purposefully putting 1 at position 70.

Now once the file got created I used the below as second step:

SORT FIELDS=(1,11,CH,A,13,7,CH,A),EQUALS
SUM FIELDS=(70,1,ZD)
END

I got a RC=04 with the message ICE152I 0 OVERFLOW DURING SUMMATION - RC=4. I got the desired output and created my file.

The above method works but looks improper to get RC=04 and its temporary fix. Can anyone help me in resolving how to get it done in proper way. I belive I can do all in one single step and multiple steps are not needed.

Please help..I need this urgent.
kpargao
 
Posts: 2
Joined: Tue Mar 08, 2011 1:35 pm
Has thanked: 0 time
Been thanked: 0 time

Re: SORT Card to sum the values of same-keys records

Postby Alissa Margulies » Tue Mar 08, 2011 9:57 pm

Hello kpargao.

You posted in the SYNCSORT forum, but based on the ICE152I message, you are running DFSORT. I am going to move your question to the appropriate forum.

Regards,
Alissa Margulies
Syncsort Mainframe Product Services
201-930-8260
zos_tech@syncsort.com
Alissa Margulies
Global moderator
 
Posts: 369
Joined: Tue Feb 26, 2008 11:15 pm
Location: USA
Has thanked: 1 time
Been thanked: 1 time

Re: SORT Card to sum the values of same-keys records

Postby skolusu » Tue Mar 08, 2011 10:28 pm

kpargao,

You need to use INREC instead of OUTREC. Use the following DFSORT JCL which will give you the desired results

//STEP0100 EXEC PGM=SORT                           
//SYSOUT   DD SYSOUT=*                             
//SORTIN   DD *                                   
----+----1----+----2----+----3----+----4----+----5-
EB111111111;2011068;XXXXXX,YYYYYY,ZZZZZ,DDDDDD     
EB111111111;2011068;XXXXXX,YYYYYY,ZZZZZ,DDDDDD     
EB111111111;2011069;XXXXXX,YYYYYY,ZZZZZ,DDDDDD     
EB122222222;2011068;XXXXXX,YYYYYY,ZZZZZ,DDDDDD     
//SORTOUT  DD SYSOUT=*                             
//SYSIN    DD *                                   
  INREC BUILD=(1,68,C';01',80:X)                   
  SORT FIELDS=(1,19,CH,A),EQUALS                   
  SUM FIELDS=(70,2,ZD)                             
//*


The output from this job is
EB111111111;2011068;XXXXXX,YYYYYY,ZZZZZ,DDDDDD                      ;02     
EB111111111;2011069;XXXXXX,YYYYYY,ZZZZZ,DDDDDD                      ;01     
EB122222222;2011068;XXXXXX,YYYYYY,ZZZZZ,DDDDDD                      ;01     
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: SORT Card to sum the values of same-keys records

Postby kpargao » Wed Mar 09, 2011 3:59 pm

THANKS KOLUSU IT WORKED AND I GOT THE DESIRED OUTPUT WITHOUT ANY OVERFLOW.
kpargao
 
Posts: 2
Joined: Tue Mar 08, 2011 1:35 pm
Has thanked: 0 time
Been thanked: 0 time

Re: SORT Card to sum the values of same-keys records

Postby vvkswm » Fri Jul 26, 2013 3:28 pm

Hi,

When you remove the EQUAL from the below code then also the output is same.

Then please can you explain me what is the use of EQUAL in JCL while sorting records.

//SYSIN DD *
INREC BUILD=(1,68,C';01',80:X)
SORT FIELDS=(1,19,CH,A),EQUALS
SUM FIELDS=(70,2,ZD)
//*

Thanks,
Vivek.
vvkswm
 
Posts: 5
Joined: Fri Jul 26, 2013 3:15 pm
Has thanked: 7 times
Been thanked: 0 time

Re: SORT Card to sum the values of same-keys records

Postby NicC » Fri Jul 26, 2013 4:33 pm

There is no EQUAL in JCL. If you are referring to sort control cards then refer to sort control cards - not JCL. Also, this topic concluded over 2 years ago. Please start a new topic in future when you are not replying to a current topic. You can always use a reference to the topic (cut and paste the URL into your post and use the URL tags. Also, use the code tags (use the search facility to search for info about them) when posting "screen-type stuff".

Refer to the sort manual for info about EQUALS. You could also search this section of the forum - again using the search function. Did you search the manual/internet/forum before posting? If not - you should have - read the rules.

Finally - Welcome to the forum!!!! (may not seem like it after that lot but now you know for the future).
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic

These users thanked the author NicC for the post:
vvkswm (Thu Aug 08, 2013 4:07 pm)
NicC
Global moderator
 
Posts: 2690
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

Re: SORT Card to sum the values of same-keys records

Postby skolusu » Fri Jul 26, 2013 10:06 pm

vvkswm wrote:Hi,

When you remove the EQUAL from the below code then also the output is same.

Then please can you explain me what is the use of EQUAL in JCL while sorting records.


vvkswm,

Just because you removed the EQUALS from the JCL doesn't mean that you are actually running with NOEQUALS. Your shop might have set it as a DEFAULT. Look at your sysout to see if EQUALS is set as DEFAULT. You can find that on ICE128I. Also remember that the sample input shown above just has 2 duplicates. If you really want to see the difference between EQUALS and NOEQUALS you need to play with input that has more duplicates.
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: SORT Card to sum the values of same-keys records

Postby vvkswm » Tue Jul 30, 2013 11:28 am

Hi,

I tried the Below JCL with NOEQUALS.Then also the output is same as EQUALS.

Please can you explain me the use of EQUALS in jcl while sorting records.

CODE:


//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
----+----1----+----2----+----3----+----4----+----5-
EB111111111;2011068;WWWWWW,YYYYYY,ZZZZZ,DDDDDD
EB111111111;2011068;XXXXXX,YYYYYY,ZZZZZ,DDDDDD
EB111111111;2011069;XXXXXX,YYYYYY,ZZZZZ,DDDDDD
EB111111111;2011070;XXXXXX,YYYYYY,ZZZZZ,DDDDDD
EB122222222;2011068;XXXXXX,YYYYYY,ZZZZZ,DDDDDD
EB122222222;2011068;XXXXXX,YYYYYY,ZZZZZ,DDDDDD
EB122222222;2011069;XXXXXX,YYYYYY,ZZZZZ,DDDDDD
//SORTOUT DD SYSOUT=*
//SYSIN DD *
INREC BUILD=(1,68,C';01',80:X)
SORT FIELDS=(1,19,CH,A),NOEQUALS
SUM FIELDS=(70,2,ZD)
/*

OUTPUT:

----+----1----+----2----+----3----+----4----+----5- ;01
EB111111111;2011068;wwwww,YYYYYY,ZZZZZ,DDDDDD ;02
EB111111111;2011069;XXXXXX,YYYYYY,ZZZZZ,DDDDDD ;01
EB111111111;2011070;XXXXXX,YYYYYY,ZZZZZ,DDDDDD ;01
EB122222222;2011068;XXXXXX,YYYYYY,ZZZZZ,DDDDDD ;02
EB122222222;2011069;XXXXXX,YYYYYY,ZZZZZ,DDDDDD ;01
vvkswm
 
Posts: 5
Joined: Fri Jul 26, 2013 3:15 pm
Has thanked: 7 times
Been thanked: 0 time

Re: SORT Card to sum the values of same-keys records

Postby BillyBoyo » Tue Jul 30, 2013 12:57 pm

If your records are identical, how are you going to tell there is any difference between EQUALS and NOEQUALS?

These users thanked the author BillyBoyo for the post:
vvkswm (Wed Jul 31, 2013 5:53 pm)
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Next

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post