Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref

Author Message
cybertaurean

New User

Joined: 22 Dec 2008
Posts: 85
Location: US

Posted: Mon Sep 05, 2011 2:06 pm    Post subject: Question on conditional SUMing using SORT

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...

Escapa

Senior Member

Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

Posted: Mon Sep 05, 2011 2:52 pm    Post subject:

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
cybertaurean

New User

Joined: 22 Dec 2008
Posts: 85
Location: US

 Posted: Mon Sep 05, 2011 4:14 pm    Post subject: Escapa!!! It worked!!! Thanks a loooooot!!!
THINKSRINIII
Warnings : 1

New User

Joined: 09 Jan 2009
Posts: 88
Location: India

Posted: Mon Sep 05, 2011 5:12 pm    Post subject:

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
cybertaurean

New User

Joined: 22 Dec 2008
Posts: 85
Location: US

Posted: Wed Sep 07, 2011 6:08 pm    Post subject:

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 -

The output should be

A41

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

Regards...
Bill Woodger

DFSORT Moderator

Joined: 09 Mar 2011
Posts: 7314

 Posted: Wed Sep 07, 2011 6:22 pm    Post subject: Reply to: Question on conditional SUMing using SORT 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?
cybertaurean

New User

Joined: 22 Dec 2008
Posts: 85
Location: US

Posted: Wed Sep 07, 2011 6:41 pm    Post subject:

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.
Bill Woodger

DFSORT Moderator

Joined: 09 Mar 2011
Posts: 7314

Posted: Wed Sep 07, 2011 6:56 pm    Post subject:

 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.
cybertaurean

New User

Joined: 22 Dec 2008
Posts: 85
Location: US

 Posted: Wed Sep 07, 2011 7:04 pm    Post subject: Thanks, Bill for your time. Let me try rummaging thru the manual then
kratos86

Active User

Joined: 17 Mar 2008
Posts: 148
Location: Anna NGR

 Posted: Wed Sep 07, 2011 7:16 pm    Post subject: 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.
Bill Woodger

DFSORT Moderator

Joined: 09 Mar 2011
Posts: 7314

 Posted: Wed Sep 07, 2011 9:37 pm    Post subject: Reply to: Question on conditional SUMing using SORT 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.
Skolusu

Senior Member

Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

Posted: Wed Sep 07, 2011 9:46 pm    Post subject:

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
cybertaurean

New User

Joined: 22 Dec 2008
Posts: 85
Location: US

Posted: Mon Sep 12, 2011 3:57 pm    Post subject:

@Bill: Yeah... I had come across that, and got that "glow" feel after it worked . Thanks!!!

Special Thanks to Kratos86 for the insight!!!

@Kolusu: Thanks, as always

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!!!
Skolusu

Senior Member

Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

Posted: Mon Sep 12, 2011 11:25 pm    Post subject: Reply to: Question on conditional SUMing using SORT

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=.... //*
cybertaurean

New User

Joined: 22 Dec 2008
Posts: 85
Location: US

 Posted: Tue Sep 13, 2011 8:46 pm    Post subject: You are right, Kolusu!!! The approach mentioned by you is definitely much more optimized. Thanks!!!
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics Extract record for change in combinat... Trinadh DFSORT/ICETOOL 6 Thu Nov 23, 2017 3:32 pm CONDITIONAL USING COMP-2 WITH PROBLEMS. JOSUEGENTIL COBOL Programming 7 Wed Nov 08, 2017 11:13 pm JOINKEYS Paired and Unpaired Sort on ... MFwhiz DFSORT/ICETOOL 7 Tue Nov 07, 2017 2:46 am question about dfsms management clas... Hervey Martinez IBM Tools 0 Tue Oct 10, 2017 6:19 pm sort records based on length exceeds ... maxsubrat DFSORT/ICETOOL 7 Wed Oct 04, 2017 4:48 pm

 © 2003-2017 IBM MAINFRAME Software Support Division
 Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us