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
 

 

To get the count of the records based on a conditions
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
ratnakumar

New User


Joined: 07 May 2009
Posts: 30
Location: Banglore

PostPosted: Fri Nov 11, 2011 6:28 pm    Post subject: To get the count of the records based on a conditions
Reply with quote

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
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Fri Nov 11, 2011 7:23 pm    Post subject: Reply to: To get the count of the records based on a conditi
Reply with quote

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.
Back to top
View user's profile Send private message
ratnakumar

New User


Joined: 07 May 2009
Posts: 30
Location: Banglore

PostPosted: Fri Nov 11, 2011 8:05 pm    Post subject:
Reply with quote

Its little confusing.. can you help me with a sample code ?

Thanks in advance.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Fri Nov 11, 2011 8:14 pm    Post subject: Reply to: To get the count of the records based on a conditi
Reply with quote

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.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Fri Nov 11, 2011 8:35 pm    Post subject:
Reply with quote

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,
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


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

PostPosted: Sat Nov 12, 2011 12:11 am    Post subject:
Reply with quote

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)))   
/*                                                                 
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Sat Nov 12, 2011 12:23 am    Post subject:
Reply with quote

Frank,
Thanks for the correction.

36_8_11.gif

Thanks,
Back to top
View user's profile Send private message
ratnakumar

New User


Joined: 07 May 2009
Posts: 30
Location: Banglore

PostPosted: Mon Nov 14, 2011 2:13 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Mon Nov 14, 2011 2:19 pm    Post subject: Reply to: To get the count of the records based on a conditi
Reply with quote

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 )
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Mon Nov 14, 2011 8:27 pm    Post subject:
Reply with quote

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,
Back to top
View user's profile Send private message
dick scherrer

Site Director


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

PostPosted: Mon Nov 14, 2011 9:39 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Tue Nov 15, 2011 12:25 am    Post subject: Reply to: To get the count of the records based on a conditi
Reply with quote

Would a code which was not F or D on your input give you this problem?
Back to top
View user's profile Send private message
ratnakumar

New User


Joined: 07 May 2009
Posts: 30
Location: Banglore

PostPosted: Tue Nov 15, 2011 1:58 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Tue Nov 15, 2011 2:09 pm    Post subject: Reply to: To get the count of the records based on a conditi
Reply with quote

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.
Back to top
View user's profile Send private message
ratnakumar

New User


Joined: 07 May 2009
Posts: 30
Location: Banglore

PostPosted: Tue Nov 15, 2011 2:15 pm    Post subject:
Reply with quote

@ 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..
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Tue Nov 15, 2011 2:23 pm    Post subject: Reply to: To get the count of the records based on a conditi
Reply with quote

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
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Tue Nov 15, 2011 2:37 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Tue Nov 15, 2011 2:43 pm    Post subject: Reply to: To get the count of the records based on a conditi
Reply with quote

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 icon_cool.gif
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Tue Nov 15, 2011 3:23 pm    Post subject:
Reply with quote

Yes... it works on Syncsort...
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Tue Nov 15, 2011 3:27 pm    Post subject: Reply to: To get the count of the records based on a conditi
Reply with quote

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


which one of the snippets posted icon_question.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 -> JCL & VSAM All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

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 split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm
No new posts abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm
This topic is locked: you cannot edit posts or make replies. Job to wait and continue based on reply yugendran COBOL Programming 5 Wed Oct 05, 2016 1:46 pm


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