To get the count of the records based on a conditions

Author Message
ratnakumar

New User

Joined: 07 May 2009
Posts: 30
Location: Banglore

Posted: Fri Nov 11, 2011 6:28 pm

Hello,

I have a huge file with cardnumber(5), and a foreign/domestic transaction indicator(1). The file looks as below:

A1234F
A1234D
A2345D
A1234F
A2345D
A2345F
A3456F

Requirement is to get count for each cardnumber based on foreign/domestic transaction

Output should like below:

CM# Foreign count Domestic count
A1234 2 1
A2345 1 2
A3456 1 0

I have sorted the file based on the Foreign or domestic indicator and next I got the count of each file in two different files. So now am confused how to get the concatenate the files and get the output in one file

I have used the below sort card to pull the counts of each CM#

 Code: SORT FIELDS=(1,5,CH,A)                                    OUTFIL REMOVECC,NODETAIL,                                  SECTIONS=(1,5,TRAILER3=(1,5,COUNT=(M10,LENGTH=19)))    /*

Please help me out, if I could do it in a single sort ? or if there is any way to concatenate the file get the required output

Thanks & Regards,
RK
Bill Woodger

Moderator Emeritus

Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

 Posted: Fri Nov 11, 2011 7:23 pm How about a build for the file, which two fields for counters which you set to one and zero appropriately. Then sort on the card number. Then SUM on the card number and the two new fields. That should all be in one sort step.
ratnakumar

New User

Joined: 07 May 2009
Posts: 30
Location: Banglore

 Posted: Fri Nov 11, 2011 8:05 pm Its little confusing.. can you help me with a sample code ? Thanks in advance.
Bill Woodger

Moderator Emeritus

Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

 Posted: Fri Nov 11, 2011 8:14 pm It shouldn't be too confusing. You can access the data before the sort is performed. You do the sort. You do the SUM. There are many good examples in the DFSORT forum. The manuals are available on the web.
sqlcode1

Active Member

Joined: 08 Apr 2010
Posts: 577
Location: USA

Posted: Fri Nov 11, 2011 8:35 pm

ratnakumar,
See if below helps. I am assuming input is 80 byte FB.

 Code: //STEP0001 EXEC PGM=SORT                                              //SORTIN   DD  *                                                      A1234F                                                                A1234D                                                                A2345D                                                                A1234F                                                                A2345D                                                                A2345F                                                                A3456F                                                                //SORTOUT  DD  SYSOUT=*                                              //SYSIN DD *                                                           INREC IFTHEN=(WHEN=(6,1,CH,EQ,C'F'),OVERLAY=(81:7C'0',C'1')),               IFTHEN=(WHEN=(6,1,CH,EQ,C'D'),OVERLAY=(89:7C'0',C'1'))         SORT FIELDS=(1,5,ZD,A),EQUALS                                         OUTFIL REMOVECC,NODETAIL,BUILD=(80X),                                        HEADER1=('CM#',3X,'FOREIGN COUNT',2X,'DOMESTIC COUNT',80:X),         SECTIONS=(1,5,                                                                  TRAILER3=(1,5,6X,TOT=(81,8,ZD,M10,LENGTH=8),                                        8X,TOT=(89,8,ZD,M10,LENGTH=8),80:X)) /*                                                                    //SYSOUT DD SYSOUT=*                                                  //*

OUTPUT
 Code: CM#   FOREIGN COUNT  DOMESTIC COUNT A1234             2               1 A2345             1               2 A3456             1               0

Thanks,
Frank Yaeger

DFSORT Developer

Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

Posted: Sat Nov 12, 2011 12:11 am

Here's a slightly improved version of sqlcode1's job. Note that you only need 1 for the totals, not 00000001. Note also that you don't need 80:X in the HEADERx or TRAILERx operands - BUILD=(80X) takes care of setting the record length.

 Code: //STEP0001 EXEC PGM=SORT                                            //SORTIN   DD  *                                                    A1234F                                                              A1234D                                                              A2345D                                                              A1234F                                                              A2345D                                                              A2345F                                                              A3456F                                                              //SORTOUT  DD  SYSOUT=*                                            //SYSIN DD *                                                         INREC IFTHEN=(WHEN=(6,1,CH,EQ,C'F'),OVERLAY=(81:C'1')),                   IFTHEN=(WHEN=(6,1,CH,EQ,C'D'),OVERLAY=(82:C'1'))             SORT FIELDS=(1,5,ZD,A),EQUALS                                       OUTFIL REMOVECC,NODETAIL,BUILD=(80X),                                      HEADER1=('CM#',3X,'FOREIGN COUNT',2X,'DOMESTIC COUNT'),            SECTIONS=(1,5,                                                                TRAILER3=(1,5,6X,TOT=(81,1,ZD,M10,LENGTH=8),                                      8X,TOT=(82,1,ZD,M10,LENGTH=8)))    /*
sqlcode1

Active Member

Joined: 08 Apr 2010
Posts: 577
Location: USA

 Posted: Sat Nov 12, 2011 12:23 am Frank, Thanks for the correction. Thanks,
ratnakumar

New User

Joined: 07 May 2009
Posts: 30
Location: Banglore

 Posted: Mon Nov 14, 2011 2:13 pm Thank you sqlcode1 & Frank for posting it. I tried the above code, but it failed with SOC 0C7. +WER999A jobname,stepname, - UNSUCCESSFUL SORT 0C7 S
enrico-sorichetti

Superior Member

Joined: 14 Mar 2007
Posts: 10873
Location: italy

 Posted: Mon Nov 14, 2011 2:19 pm the WER messages indicate that You are using SYNCSORT Frank is a DFSORT developer, since SYNCSORT and DFSORT are competitive products He will not answer SYNCSORT related questions topic moved to the JCL forum ( the one appropriate for SYNCSORT issues )
sqlcode1

Active Member

Joined: 08 Apr 2010
Posts: 577
Location: USA

 Posted: Mon Nov 14, 2011 8:27 pm ratnakumar, I don't have Syncsort or its documentation but I believe all the functions used in the sort above should be available in Syncsort as well. If you don't mind, could you post your entire error message? Thanks,
dick scherrer

Moderator Emeritus

Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

 Posted: Mon Nov 14, 2011 9:39 pm Hello, If the process raises an 0c7 abend it is because of an invalid numeric value. Two reasons this could be caused are corrupt data or incorrect data positions in the sort control statements. As requested, you need to post (all of) the diagnostic information generated by the run.
Bill Woodger

Moderator Emeritus

Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

 Posted: Tue Nov 15, 2011 12:25 am Would a code which was not F or D on your input give you this problem?
ratnakumar

New User

Joined: 07 May 2009
Posts: 30
Location: Banglore

Posted: Tue Nov 15, 2011 1:58 pm

Here is the complete information in JESMSGLOG

 Code: +WER999A jobname,STEP01A ,        -  UNSUCCESSFUL SORT 0C7 S +AB400A      ===== ABEND-AID =====  751                         REPORT WRITTEN TO: SYS4.ABENDAID.REPORT.DIRECTRY             REPORT NUMBER: 0355491                                       JOB-jobname STEP-STEP01A                                     ABEND CODE=S0C7                                                 ACCESS YOUR ABEND-AID REPORTS BY EXECUTING                   AA FROM THE ISPF PRIMARY OPTION MENU.                    IEA995I SYMPTOM DUMP OUTPUT  982                              SYSTEM COMPLETION CODE=0C7  REASON CODE=00000000               TIME=02.31.45  SEQ=49489  CPU=0000  ASID=0103                 PSW AT TIME OF ERROR  078D1000   80019582  ILC 4  INTC 07       NO ACTIVE MODULE FOUND                                       NAME=UNKNOWN                                                 DATA AT PSW  0001957C - 000F4430  A9E04710  A9A69120         AR/GR 0: 00000000/00000000   1: 00000000/0001B970                   2: 00000000/00000051   3: 00000000/0000000F                   4: 00000000/00000008   5: 00000000/0001C698                   6: 00000000/00023D70   7: 00000000/00023E00                   8: 00000000/00023F18   9: 00000000/00000000                   A: 00000000/00018C4A   B: 00000000/00017C4A                   C: 00000000/0001C698   D: 00000000/0000C5CC

Information in the ABENDAID:

A data exception, S0C7, occurred during execution of a program in
module SS13X02C.
Bill Woodger

Moderator Emeritus

Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

 Posted: Tue Nov 15, 2011 2:09 pm We need the sort cards you used. Are you getting a dump? We don't need that, but you could look to see if there is such a thing as a "current record" or similar. EDIT: Also all the WER messages please.
ratnakumar

New User

Joined: 07 May 2009
Posts: 30
Location: Banglore

Posted: Tue Nov 15, 2011 2:15 pm

@ Bill..

sort card :

 Code: INREC IFTHEN=(WHEN=(6,1,CH,EQ,C'F'),OVERLAY=(81:C'1')),               IFTHEN=(WHEN=(6,1,CH,EQ,C'D'),OVERLAY=(82:C'1'))           SORT FIELDS=(1,5,ZD,A),EQUALS                                   OUTFIL REMOVECC,NODETAIL,BUILD=(80X),                                  HEADER1=('CM#',3X,'FOREIGN COUNT',2X,'DOMESTIC COUNT'),         SECTIONS=(1,5,                                                            TRAILER3=(1,5,6X,TOT=(81,1,ZD,M10,LENGTH=8),                                  8X,TOT=(82,1,ZD,M10,LENGTH=8)))

I checked for current , last record but could not get any information. One interesting line I found was mentioned below:

 Code: *******************************************                          *           Call Trace Summary            *                          *******************************************                                                                                                *******Calling*******  ****Return****           *******Called******** Load-Mod  Program      Type     Value           Program      Load-Mod                                                                       *SYSTEM                                Links to QKQMAIN      SYNCSORT

The program causing the error is not on the calling
chain.

Thank you..
enrico-sorichetti

Superior Member

Joined: 14 Mar 2007
Posts: 10873
Location: italy

Posted: Tue Nov 15, 2011 2:23 pm

might be SYNCSORT is picky about lengths and true numerics
try this one

 Code: ****** ***************************** Top of Data ******************************  000001 //ENRICO1  JOB NOTIFY=&SYSUID,                                           000002 //             MSGLEVEL=(1,1),CLASS=A,MSGCLASS=H                         000003 //*                                                                       000004 //ICE     EXEC PGM=SORT                                                   000005 //SYSOUT    DD SYSOUT=*                                                   000006 //SYSPRINT  DD SYSOUT=*                                                   000007 //SORTIN    DD *                                                         000008 A1234F                                                                   000009 A1234D                                                                   000010 A2345D                                                                   000011 A3456F                                                                   000012 //SORTOUT   DD SYSOUT=*                                                   000013 //SYSIN     DD *                                                         000014   INREC IFTHEN=(WHEN=(6,1,CH,EQ,C'F'),OVERLAY=(81:C'10')),               000015         IFTHEN=(WHEN=(6,1,CH,EQ,C'D'),OVERLAY=(81:C'01'))                 000016   SORT  FIELDS=(1,5,CH,A),EQUALS                                         000017   OUTFILE REMOVECC,NODETAIL,BUILD=(80X),                                 000018         HEADER1=('CM#',3X,'FOREIGN COUNT',2X,'DOMESTIC COUNT'),           000019         SECTIONS=(1,5,                                                   000020                   TRAILER3=(1,5,6X,TOT=(81,1,ZD,M10,LENGTH=8),           000021                                 8X,TOT=(82,1,ZD,M10,LENGTH=8)))           000022 //                                                                       ****** **************************** Bottom of Data ****************************

result
 Code: ********************************* TOP OF DATA ********************************** CM#   FOREIGN COUNT  DOMESTIC COUNT                                              A1234             1               1                                              A2345             0               1                                              A3456             1               0                                              ******************************** BOTTOM OF DATA ********************************

notice the difference from the previous examples
the overlay is for 2 chars to avoid a short record for the F case and to provide a proper numeric for a ZD
the sort statement has been changed from ZD to CH

tested with dfsort to see that the correct results are still achieved

and ...
use the code tags to make things more readable for people willing to help
Your previous posts were edited to provide them
Bill Woodger

Moderator Emeritus

Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

Posted: Tue Nov 15, 2011 2:37 pm

 ratnakumar wrote: [...] One interesting line I found was mentioned below: ******************************************* * Call Trace Summary * ******************************************* *******Calling******* ****Return**** *******Called******** Load-Mod Program Type Value Program Load-Mod *SYSTEM Links to QKQMAIN SYNCSORT The program causing the error is not on the calling chain. [...]

Well spotted. Good to seem someone who can post stuff for us which seems "unusual".
However, since it is a S0C7 I doubt it is of significance in this instance. I suspect that Syncsort has its own calling conventions, which Abend-Aid is unaware of. Without the source, there's not much we can do with the information if it was there :-)

I think go with enrico's code and see what happens. If that fails, check that there are only D and F codes on your data.
enrico-sorichetti

Superior Member

Joined: 14 Mar 2007
Posts: 10873
Location: italy

Posted: Tue Nov 15, 2011 2:43 pm

anyway
 Quote: 0C7 Data exception. The reason code is whatever the data exception code is in field SDWADXC in the SDWA (systems diagnostic work area).

the data is not what You think
gylbharat

Active Member

Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

 Posted: Tue Nov 15, 2011 3:23 pm Yes... it works on Syncsort...
enrico-sorichetti

Superior Member

Joined: 14 Mar 2007
Posts: 10873
Location: italy

Posted: Tue Nov 15, 2011 3:27 pm

 Quote: Yes... it works on Syncsort...

which one of the snippets posted
ratnakumar

New User

Joined: 07 May 2009
Posts: 30
Location: Banglore

 Posted: Tue Nov 15, 2011 5:09 pm ok.. let me check
ratnakumar

New User

Joined: 07 May 2009
Posts: 30
Location: Banglore

 Posted: Tue Nov 15, 2011 5:18 pm It worked.. Thanks you guys..
Bill Woodger

Moderator Emeritus

Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

 Posted: Tue Nov 15, 2011 5:49 pm Thanks for letting us know. Can you confirm that it was enrico's code you used? May help others with similar problems.
ratnakumar

New User

Joined: 07 May 2009
Posts: 30
Location: Banglore

 Posted: Tue Nov 15, 2011 7:03 pm Yes, I used enrico's code. and it worked successfully
enrico-sorichetti

Superior Member

Joined: 14 Mar 2007
Posts: 10873
Location: italy

Posted: Tue Nov 15, 2011 7:12 pm

well, it would be useful if some good soul could check about the pickiness of SYNCSORT

I tested with dsfort also for records not matching the F or the D and I got 0 for the totals

can anybody please confirm that running

 Code: ****** ***************************** Top of Data ******************************  000001 //ENRICO1  JOB NOTIFY=&SYSUID,                                           000002 //             MSGLEVEL=(1,1),CLASS=A,MSGCLASS=H                         000003 //*                                                                       000004 //ICE     EXEC PGM=SORT                                                   000005 //SYSOUT    DD SYSOUT=*                                                   000006 //SYSPRINT  DD SYSOUT=*                                                   000007 //SORTIN    DD *                                                         000008 A1234F                                                                   000009 A1234D                                                                   000010 A2345D                                                                   000011 A3456F                                                                   000012 Z0000                                                                     000013 //SORTOUT   DD SYSOUT=*                                                   000014 //SYSIN     DD *                                                         000015   INREC IFTHEN=(WHEN=(6,1,CH,EQ,C'F'),OVERLAY=(81:C'1')),                 000016         IFTHEN=(WHEN=(6,1,CH,EQ,C'D'),OVERLAY=(82:C'1'))                 000017   SORT  FIELDS=(1,5,ZD,A),EQUALS                                         000018   OUTFILE REMOVECC,NODETAIL,BUILD=(80X),                                 000019         HEADER1=('CM#',3X,'FOREIGN COUNT',2X,'DOMESTIC COUNT'),           000020         SECTIONS=(1,5,                                                   000021                   TRAILER3=(1,5,6X,TOT=(81,1,ZD,M10,LENGTH=8),           000022                                 8X,TOT=(82,1,ZD,M10,LENGTH=8)))           000023 //                                                                       ****** **************************** Bottom of Data ****************************

SYNCSORT will abend

while DFSORT will happily digest everything
 Code: ********************************* TOP OF DATA ********************************** CM#   FOREIGN COUNT  DOMESTIC COUNT                                              A1234             1               1                                              A2345             0               1                                              A3456             1               0                                              Z0000             0               0                                              ******************************** BOTTOM OF DATA ********************************
 View Bookmarks All times are GMT + 6 Hours
 Goto page 1, 2  Next

 Topic Forum Replies Similar Topics To get the count of rows for every 1 ... DB2 3 Compare only first records of the fil... SYNCSORT 7 Pulling a fixed number of records fro... DB2 2 To find whether record count are true... DFSORT/ICETOOL 6 How to load to DB2 with column level ... DB2 6
Search our Forums:

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