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

Question on conditional SUMing using SORT
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
cybertaurean

New User


Joined: 22 Dec 2008
Posts: 87
Location: US
PostPosted: Mon Sep 05, 2011 2:06 pm
 Reply with quote
Hi,

I have a file as shown below -

Code:
1 D
1 _
1 _
2 D
2 D
2 _
3 _
4 D
4 _


I need to eliminate the duplicates on the first field (i.e. 1,2,3,4...) and have the the following columns added -

a. Count of first field
b. Count of first field with a 'D' in the second column

For the above data, the output should be -

Code:
1 3 1
2 3 2
3 1 0
4 2 1


We are looking at sum on elimination of duplicates + conditional count (D) here.

For the summation part, I can use an INREC with 1's padded and SUM on the padded fields with SORT FIELDS on first field.

How do I add the second column which would increment for all D's found within a unique first field.

Regards...
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA
PostPosted: Mon Sep 05, 2011 2:52 pm
 Reply with quote
Hi,
You can use something like this..
Code:

//STEP0100 EXEC PGM=SORT                                           
//SYSOUT   DD SYSOUT=*                                             
//SORTIN   DD *                                                     
1 D                                                                 
1 _                                                                 
1 _                                                                 
2 D                                                                 
2 D                                                                 
2 _                                                                 
3 _                                                                 
4 D                                                                 
4 _                                                                 
//SORTOUT  DD SYSOUT=*                                             
//SYSIN    DD *                                                     
 INREC IFTHEN=(WHEN=INIT,OVERLAY=(11:C'00000001',19:C'00000000')), 
       IFTHEN=(WHEN=(3,1,CH,EQ,C'D'),OVERLAY=(19:C'00000001'))   
 SORT FIELDS=(1,1,CH,A)                                             
 SUM  FIELDS=(11,8,ZD,19,8,ZD)                                     
 
/*                                                                 

Output will be
Code:

1 D       0000000300000001
2 D       0000000300000002
3 _       0000000100000000
4 D       0000000200000001

You can also try for solution with use of OUTFIL sections
Back to top
View user's profile Send private message
cybertaurean

New User


Joined: 22 Dec 2008
Posts: 87
Location: US
PostPosted: Mon Sep 05, 2011 4:14 pm
 Reply with quote
Escapa!!!

It worked!!! Thanks a loooooot!!! icon_smile.gif icon_smile.gif icon_smile.gif
Back to top
View user's profile Send private message
THINKSRINIII
Warnings : 1
New User


Joined: 09 Jan 2009
Posts: 88
Location: India
PostPosted: Mon Sep 05, 2011 5:12 pm
 Reply with quote
OUTFIL and SECTIONS:
Code:

//SYSIN    DD *                                               
  SORT FIELDS=(1,1,CH,A)                                     
  INREC IFTHEN=(WHEN=(3,1,CH,EQ,C'D'),OVERLAY=(10:C'1'))     
  OUTFIL NODETAIL,SECTIONS(1,1,                               
         TRAILER3=(1:1,1,COUNT=(EDIT=(STT),SIGNS=(+,-)),     
                   TOTAL=(10,1,SFF,EDIT=(STT),SIGNS=(+,-)))) 


Output
Code:

1+03+01
2+03+02
3+01+00
4+02+01
Back to top
View user's profile Send private message
cybertaurean

New User


Joined: 22 Dec 2008
Posts: 87
Location: US
PostPosted: Wed Sep 07, 2011 6:08 pm
 Reply with quote
Is it not possible to add multiple conditions in the SORT. Say, I have a condition like given below -


Code:
IFTHEN=(WHEN=(1,1,CH,EQ,C'A'),
        OVERLAY=(3:C'00001')),                     
IFTHEN=(WHEN=(1,1,CH,EQ,C'A',AND,2,1,CH,EQ,C'D '),
        OVERLAY=(4:C'00001')),                     


For a sample output like shown below -

Code:
A_
A_
AD
A_


The output should be

A41

A - the value
3 - The # of times it occurs
1 - The # of times it occurs with a 'D'

Regards...
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
PostPosted: Wed Sep 07, 2011 6:22 pm
 Reply with quote
Is it not possible? Is that not to avoid the Yes or No answer?

It seems only a small variation on your original query, and therefore of the code provided for you. Have you tried it?
Back to top
View user's profile Send private message
cybertaurean

New User


Joined: 22 Dec 2008
Posts: 87
Location: US
PostPosted: Wed Sep 07, 2011 6:41 pm
 Reply with quote
Hi Bill,

I tried it out and it seems like, if SORT finds the first condition matches for a particular row, it would not take into consideration that row for the next evaluation.

The above could be due to the fact that the conditions are not exactly mutually exclusive.

Thoughts???

I'm tried with -

Code:
IFTHEN=(WHEN=(1,1,CH,EQ,C'A'AND,2,1,CH,NE,C'D '),
        OVERLAY=(3:C'00001')),                     
IFTHEN=(WHEN=(1,1,CH,EQ,C'A',AND,2,1,CH,EQ,C'D '),
        OVERLAY=(4:C'00001')),


A and not 'D'
A and 'D'

This above criteria - eventhough, mutually exclusive - didn't work. The count for the first instance was set to 0 itself, whereas the second field came up just fine.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
PostPosted: Wed Sep 07, 2011 6:56 pm
 Reply with quote
cybertaurean wrote:
[...]

I tried it out and it seems like, if SORT finds the first condition matches for a particular row, it would not take into consideration that row for the next evaluation.
[...]


When there is something that "should" reasonably work, and it doesn't, that is the time to hit the manual to find out what you are doing wrong. If you take your above thought to the manual and read-up on the IFTHEN, you should be able to get to what you want.

"Why doesn't that work?" is usually better as "What I am not doing properly?" and sometimes "What idiotic rubbish did I invent to think that that would work?". The manual usually resolves which of the real questions it is.
Back to top
View user's profile Send private message
cybertaurean

New User


Joined: 22 Dec 2008
Posts: 87
Location: US
PostPosted: Wed Sep 07, 2011 7:04 pm
 Reply with quote
Thanks, Bill for your time. Let me try rummaging thru the manual then icon_smile.gif
Back to top
View user's profile Send private message
kratos86

Active User


Joined: 17 Mar 2008
Posts: 148
Location: Anna NGR
PostPosted: Wed Sep 07, 2011 7:16 pm
 Reply with quote
By default the processing of IFTHEN stops when a condition is satisfied. In order to continue the validation you need to code HIT=NEXT parameter. Check your manual/search the forum on more details about this parameter.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
PostPosted: Wed Sep 07, 2011 9:37 pm
 Reply with quote
Mmmm.... Kratos gave you a bigger "hint".

So you were right in how you were thinking, and are now aware how to code for it. Hopefully you found it in the manual yourself first, and got that extra "glow" from confirming it for yourself.

It's a useful process, let's call it a tool. Think, manual, think again, manual - then colleagues, techies, ibmmainframes. Use the web at any point you like as well. That'll get the majority of your problems solved, and quicker each time as you get more experience with it. Applies to all software/systems.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Wed Sep 07, 2011 9:46 pm
 Reply with quote
cybertaurean,

As Kratos86 pointed out you need Hit=NEXT to evaluate the next ifthen when the first condition is true. Read here for IFTHEN with HIT=NEXT

A better coding would be

Code:

//STEP0200 EXEC PGM=SORT                                     
//SYSOUT   DD SYSOUT=*                                       
//SORTIN   DD *                                             
A_                                                           
A_                                                           
AD                                                           
A_                                                           
//SORTOUT  DD SYSOUT=*                                       
//SYSIN    DD *                                             
  SORT FIELDS=COPY                                           
  INCLUDE COND=(1,1,CH,EQ,C'A')                             
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(4:C'00001',X,5C'0')),     
        IFTHEN=(WHEN=(2,1,CH,EQ,C'D'),OVERLAY=(10:C'00001'))
//*


which would produce this

Code:

A_ 00001 00000
A_ 00001 00000
AD 00001 00001
A_ 00001 00000


Now you can sum the fields at pos 4 and pos 10 for a length of 5 bytes which would give you the desired results. In order to the summing you need to sort which is a moot point as you only A records. So it is better to use the reporting features and get the counts.

Code:

//STEP0400 EXEC PGM=SORT                                             
//SYSOUT   DD SYSOUT=*                                               
//SORTIN   DD *                                                     
A_                                                                   
A_                                                                   
AD                                                                   
A_                                                                   
//SORTOUT  DD SYSOUT=*                                               
//SYSIN    DD *                                                     
  SORT FIELDS=COPY                                                   
  INCLUDE COND=(1,1,CH,EQ,C'A')                                     
  INREC IFTHEN=(WHEN=(2,1,CH,EQ,C'D'),OVERLAY=(3:C'1'))             
  OUTFIL REMOVECC,NODETAIL,                                         
  TRAILER1=(1,1,2X,COUNT=(M11,LENGTH=5),2X,TOT=(3,1,ZD,M11,LENGTH=5))
//*


will produce

Code:

A  00004  00001
Back to top
View user's profile Send private message
cybertaurean

New User


Joined: 22 Dec 2008
Posts: 87
Location: US
PostPosted: Mon Sep 12, 2011 3:57 pm
 Reply with quote
@Bill: Yeah... I had come across that, and got that "glow" feel after it worked icon_biggrin.gif. Thanks!!!

Special Thanks to Kratos86 for the insight!!!

@Kolusu: Thanks, as always icon_biggrin.gif icon_biggrin.gif icon_biggrin.gif

BTW... Had to be tweaked per the requirement (HIT NEXT at the right places)... The entire thingy listed below (with all the "confidential" (if any) stuff removed, ofcourse) -

Code:
  SORT FIELDS=(1,34,CH,A)                                 
  SUM FIELDS=(37,5,ZD,42,5,ZD,                             
              47,5,ZD,52,5,ZD,                             
              57,5,ZD,62,5,ZD,                             
              67,5,ZD,72,5,ZD,                             
              77,5,ZD,82,5,ZD,                             
              87,5,ZD,92,5,ZD,                             
              97,5,ZD,102,5,ZD,                           
              107,5,ZD,112,5,ZD,                           
              117,5,ZD,122,5,ZD,                           
              127,5,ZD,132,5,ZD,                           
              137,5,ZD,142,5,ZD)                           
                                                           
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(37:C'00000',42:C'00000',
                                   47:C'00000',52:C'00000',
                                   57:C'00000',62:C'00000',   
                                   67:C'00000',72:C'00000',   
                                   77:C'00000',82:C'00000',   
                                   87:C'00000',92:C'00000',   
                                   97:C'00000',102:C'00000',   
                                   107:C'00000',112:C'00000', 
                                   117:C'00000',122:C'00000', 
                                   127:C'00000',132:C'00000', 
                                   137:C'00000',142:C'00000')),
                                                               
        IFTHEN=(WHEN=(33,2,CH,EQ,C'AB',AND,35,2,CH,EQ,C'D '), 
                OVERLAY=(42:C'00001'),HIT=NEXT),               
        IFTHEN=(WHEN=(33,2,CH,EQ,C'AB'),                       
                OVERLAY=(37:C'00001')),   
                   
        IFTHEN=(WHEN=(33,2,CH,EQ,C'CD',AND,35,2,CH,EQ,C'D '), 
                OVERLAY=(52:C'00001'),HIT=NEXT),               
        IFTHEN=(WHEN=(33,2,CH,EQ,C'CD'),                     
                OVERLAY=(47:C'00001')),                     

        IFTHEN=(WHEN=(33,2,CH,EQ,C'EF',AND,35,2,CH,EQ,C'D '),
                OVERLAY=(62:C'00001'),HIT=NEXT),             
        IFTHEN=(WHEN=(33,2,CH,EQ,C'EF'),                     
                OVERLAY=(57:C'00001')),                     

        IFTHEN=(WHEN=(33,2,CH,EQ,C'GH',AND,35,2,CH,EQ,C'D '),
                OVERLAY=(72:C'00001'),HIT=NEXT),             
        IFTHEN=(WHEN=(33,2,CH,EQ,C'GH'),                     
                OVERLAY=(67:C'00001')),                     

        IFTHEN=(WHEN=(33,2,CH,EQ,C'IJ',AND,35,2,CH,EQ,C'D '),
                OVERLAY=(82:C'00001'),HIT=NEXT),             
        IFTHEN=(WHEN=(33,2,CH,EQ,C'IJ'),                     
                OVERLAY=(77:C'00001')),                     

        IFTHEN=(WHEN=(33,2,CH,EQ,C'KL',AND,35,2,CH,EQ,C'D '),
                OVERLAY=(92:C'00001'),HIT=NEXT),             
        IFTHEN=(WHEN=(33,2,CH,EQ,C'KL'),                     
                OVERLAY=(87:C'00001')),                     

        IFTHEN=(WHEN=(33,2,CH,EQ,C'MN',AND,35,2,CH,EQ,C'D '),
                OVERLAY=(102:C'00001'),HIT=NEXT),           
        IFTHEN=(WHEN=(33,2,CH,EQ,C'MN'),                     
                OVERLAY=(97:C'00001')),                     

        IFTHEN=(WHEN=(33,2,CH,EQ,C'OP',AND,35,2,CH,EQ,C'D '),
                OVERLAY=(112:C'00001'),HIT=NEXT),           
        IFTHEN=(WHEN=(33,2,CH,EQ,C'OP'),                     
                OVERLAY=(107:C'00001')),                     

        IFTHEN=(WHEN=(33,2,CH,EQ,C'QR',AND,35,2,CH,EQ,C'D '),
                OVERLAY=(122:C'00001'),HIT=NEXT),           
        IFTHEN=(WHEN=(33,2,CH,EQ,C'QR'),                     
                OVERLAY=(117:C'00001')),                     

        IFTHEN=(WHEN=(33,2,CH,EQ,C'ST',AND,35,2,CH,EQ,C'D '),
                OVERLAY=(132:C'00001'),HIT=NEXT),           
        IFTHEN=(WHEN=(33,2,CH,EQ,C'ST'),                     
                OVERLAY=(127:C'00001')),                     

        IFTHEN=(WHEN=(33,2,CH,EQ,C'UV',AND,35,2,CH,EQ,C'D '),
                OVERLAY=(142:C'00001'),HIT=NEXT),           
        IFTHEN=(WHEN=(33,2,CH,EQ,C'UV'),                     
                OVERLAY=(137:C'00001'))                     
                                                             
  OUTREC FIELDS=(1,4,C',',5,10,C',',15,10,C',',25,8,C',',   
                 37,5,C',',42,5,C',',47,5,C',',52,5,C',',   
                 57,5,C',',62,5,C',',67,5,C',',72,5,C',',   
                 77,5,C',',82,5,C',',87,5,C',',92,5,C',',   
                 97,5,C',',102,5,C',',107,5,C',',112,5,C',',
                 117,5,C',',122,5,C',',127,5,C',',132,5,C',',
                 137,5,C',',142,5)


Thanks!!!
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Mon Sep 12, 2011 11:25 pm
 Reply with quote
cybertaurean,

I see that you are validating 4 contiguous bytes, so why not search the entire 4 bytes as a single filed? If you have any data in pos 35 for 2 bytes you can force it to have spaces when the field doesn't contain 'D ' and you can avoid coding the HIT=NEXT on every IFTHEN statement.

The initial When=INIT will force 2 spaces in pos 35 if the value does not contain 'D ' , with a CHANGE parm. By doing so you are validating the complete 4 bytes as a single field and it becomes unique. Also you don't have to specify the positions and complete length to initialize the values to zero. You have 22 fields to be initialized to zero each with a length of 5 bytes (22 X 5 = 110 ). So 110C'0' will put 110 zeros from pos 37. since your intention is to put '1' on the last byte you don't have to specify the entire 5 bytes on the overlay as we already initialized the values to zero, you can simply code the pos of the last byte to have '1' . You have to overlay '1' for every 5 bytes starting from pos 41. ie ( 41, 46, 51, 56,....146).

Also since we know the summing fields all have the same format we can specify it just once using FORMAT=ZD like shown below.


ex:
Code:

//SYSIN    DD *                                                     
  INREC IFTHEN=(WHEN=INIT,                                         
  BUILD=(1,34,35,2,CHANGE=(2,C'D ',C'D '),NOMATCH=(C'  '),110C'0')),
  IFTHEN=(WHEN=(33,4,CH,EQ,C'AB  '),OVERLAY=(041:C'1')),         
  IFTHEN=(WHEN=(33,4,CH,EQ,C'ABD '),OVERLAY=(046:C'1')) 

  .....all your IFthen conditions
       
  SORT FIELDS=(1,34,CH,A)
  SUM FIELDS=(037,5,042,5,                                         
              047,5,052,5,                                         
              057,5,062,5,                                         
              067,5,072,5,                                         
              077,5,082,5,                                         
              087,5,092,5,                                         
              097,5,102,5,                                         
              107,5,112,5,                                         
              117,5,122,5,                                         
              127,5,132,5,                                         
              137,5,142,5),FORMAT=ZD                               

  OUTREC FIELDS=....
//*
Back to top
View user's profile Send private message
cybertaurean

New User


Joined: 22 Dec 2008
Posts: 87
Location: US
PostPosted: Tue Sep 13, 2011 8:46 pm
 Reply with quote
You are right, Kolusu!!! The approach mentioned by you is definitely much more optimized. Thanks!!! icon_smile.gif
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
No new posts Need to set RC4 through JCL SORT DFSORT/ICETOOL 5
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts JCL sort card - get first day and las... JCL & VSAM 9
No new posts Sort First/last record of a subset th... DFSORT/ICETOOL 7
No new posts how to calculate SUM value for VB fil... DFSORT/ICETOOL 1
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us