Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Question on conditional SUMing using SORT

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
cybertaurean

New User


Joined: 22 Dec 2008
Posts: 85
Location: US

PostPosted: Mon Sep 05, 2011 2:06 pm    Post subject: Question on conditional SUMing using SORT
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    Post subject:
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: 85
Location: US

PostPosted: Mon Sep 05, 2011 4:14 pm    Post subject:
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    Post subject:
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: 85
Location: US

PostPosted: Wed Sep 07, 2011 6:08 pm    Post subject:
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7230

PostPosted: Wed Sep 07, 2011 6:22 pm    Post subject: Reply to: Question on conditional SUMing using SORT
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: 85
Location: US

PostPosted: Wed Sep 07, 2011 6:41 pm    Post subject:
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7230

PostPosted: Wed Sep 07, 2011 6:56 pm    Post subject:
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: 85
Location: US

PostPosted: Wed Sep 07, 2011 7:04 pm    Post subject:
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    Post subject:
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7230

PostPosted: Wed Sep 07, 2011 9:37 pm    Post subject: Reply to: Question on conditional SUMing using SORT
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    Post subject:
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: 85
Location: US

PostPosted: Mon Sep 12, 2011 3:57 pm    Post subject:
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    Post subject: Reply to: Question on conditional SUMing using SORT
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: 85
Location: US

PostPosted: Tue Sep 13, 2011 8:46 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm
No new posts How to delete second instance from Fl... Gunapala CN DFSORT/ICETOOL 6 Tue Oct 18, 2016 11:42 pm
No new posts abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us