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
 

 

Count and Average

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
shrivatsa
Warnings : 1

Active User


Joined: 17 Mar 2006
Posts: 171
Location: Bangalore

PostPosted: Tue Jun 07, 2011 7:05 pm    Post subject: Count and Average
Reply with quote

Hi,

I have input file as below
LRECL=80 FB

Code:
Hello World      01RES
Hello World      01RES
Hello World      01RES
Hello World      01RES
Hello World      02RES
Hello World      02RES
Hello World      02RES
Hello World      02RES
Hello World      02RES
TRAINING         03TER
TRAINING         03TER
TRAINING         03TER


Output report Required in below format
Code:

DEPT           COUNT   AVERAGE%
---------      -----  ---------
Hello World      04   33.33
Hello World      05   41.66
TRAINING         03   25.00


Average is calculated as (number of occurance of record per group of DEPT and number by total number of records *100)
Back to top
View user's profile Send private message

Skolusu

Senior Member


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

PostPosted: Tue Jun 07, 2011 9:55 pm    Post subject:
Reply with quote

shrivatsa,

The following DFSORT JCL will give you the desired results.

Code:

//STEP0100 EXEC PGM=SORT                                 
//SYSOUT   DD SYSOUT=*                                   
//INA      DD *                                         
HELLO WORLD      01RES                                   
HELLO WORLD      01RES                                   
HELLO WORLD      01RES                                   
HELLO WORLD      01RES                                   
HELLO WORLD      02RES                                   
HELLO WORLD      02RES                                   
HELLO WORLD      02RES                                   
HELLO WORLD      02RES                                   
HELLO WORLD      02RES                                   
TRAINING         03TER                                   
TRAINING         03TER                                   
TRAINING         03TER                                   
//INB      DD *                                         
HELLO WORLD      01RES                                   
HELLO WORLD      01RES                                   
HELLO WORLD      01RES                                   
HELLO WORLD      01RES                                   
HELLO WORLD      02RES                                   
HELLO WORLD      02RES                                   
HELLO WORLD      02RES                                   
HELLO WORLD      02RES                                   
HELLO WORLD      02RES                                   
TRAINING         03TER                                   
TRAINING         03TER                                   
TRAINING         03TER                                   
//SORTOUT  DD SYSOUT=*                                   
//SYSIN    DD *                                                   
  JOINKEYS F1=INA,FIELDS=(20,1,A)                                 
  JOINKEYS F2=INB,FIELDS=(01,1,A)                                 
  JOIN UNPAIRED                                                   
  REFORMAT FIELDS=(F1:1,20,F2:2,8)                                 
  INREC BUILD=(1,28,X,C'00000001')                                 
  SORT FIELDS=(1,19,CH,A)                                         
  SUM FIELDS=(30,8,ZD)                                             
  OUTREC OVERLAY=(40:(+10000,MUL,30,8,ZD),DIV,21,8,ZD,EDIT=(TT.TT))
  OUTFIL REMOVECC,NODETAIL,BUILD=(80X),                           
  HEADER2=('DEPT                    COUNT  AVERAGE%',/,           
           '-------------------- --------  --------'),             
  SECTIONS=(1,19,TRAILER3=(1,19,22:30,8,35:40,5))                 
//JNF1CNTL DD *                                                   
  INREC BUILD=(1,19,X)                                             
//JNF2CNTL DD *                                                   
  INREC BUILD=(X,C'00000001')                                     
  SUM FIELDS=(2,8,ZD)                                             
//*


The output from this job is
Code:

DEPT                    COUNT  AVERAGE%
-------------------- --------  --------
HELLO WORLD      01  00000004     33.33
HELLO WORLD      02  00000005     41.66
TRAINING         03  00000003     25.00
Back to top
View user's profile Send private message
shrivatsa
Warnings : 1

Active User


Joined: 17 Mar 2006
Posts: 171
Location: Bangalore

PostPosted: Tue Jun 07, 2011 10:03 pm    Post subject: Reply to: DFSORT Count and Average
Reply with quote

Hi Skolusu,

Very perfect. Thanks for your time in providing the solution.
I was playing around with one file. Can you please tell me what has given you the idea to achieve this solution by using same input file 2 times?
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Tue Jun 07, 2011 10:08 pm    Post subject: Re: Reply to: DFSORT Count and Average
Reply with quote

shrivatsa wrote:
Hi Skolusu,

Very perfect. Thanks for your time in providing the solution.
I was playing around with one file. Can you please tell me what has given you the idea to achieve this solution by using same input file 2 times?


shrivatsa,

You can still do with 1 file but would require 2 passes of data. I just eliminated the 2 passes and clubbed the solution into 1 pass. Nothing fancy in there as it is just another programming approach.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Jun 08, 2011 12:46 pm    Post subject:
Reply with quote

Hi Skolusu,

I ran this job but got the below error
Code:

PARMEXIT : VSCORET=12M,MSG=SC                                                 
SYSIN :                                                                       
  JOINKEYS F1=INA,FIELDS=(20,1,A)                                             
           *                                                                   
  JOINKEYS F2=INB,FIELDS=(01,1,A)                                             
           *                                                                   
  JOIN UNPAIRED                                                               
  REFORMAT FIELDS=(F1:1,20,F2:2,8)                                             
  INREC BUILD=(1,28,X,C'00000001')                                             
  SORT FIELDS=(1,19,CH,A)                                                     
  SUM FIELDS=(30,8,ZD)                                                         
  OUTREC OVERLAY=(40:(+10000,MUL,30,8,ZD),DIV,21,8,ZD,EDIT=(TT.TT))           
  OUTFIL REMOVECC,NODETAIL,BUILD=(80X),                                       
  HEADER2=('DEPT                    COUNT  AVERAGE%',/,                       
           '-------------------- --------  --------'),                         
  SECTIONS=(1,19,TRAILER3=(1,19,22:30,8,35:40,5))                             
WER161B  ALTERNATE PARM USED                                                   
WER268A  JOINKEYS STATEMENT: SYNTAX ERROR                                     
WER268A  JOINKEYS STATEMENT: SYNTAX ERROR                                     
WER211B  SYNCSMF  CALLED BY SYNCSORT; RC=0000                                 
WER449I  SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE                                 
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Jun 08, 2011 1:11 pm    Post subject:
Reply with quote

WER449I SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Wed Jun 08, 2011 2:07 pm    Post subject:
Reply with quote

Look at the syntax of JOINKEYS for SyncSort and all will be forgiven, provided you're using the realse of the product which supports the JOINKEYS functionality.
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: Wed Jun 08, 2011 9:13 pm    Post subject:
Reply with quote

Quote:
Look at the syntax of JOINKEYS for SyncSort and all will be forgiven, provided you're using the realse of the product which supports the JOINKEYS functionality.


This statement is misleading. DFSORT supports JNFxCNTL for modifying the records from the input files before they are joined. Syncsort does not support JNFxCNTL. So with Syncsort, you'd actually need multiple steps to do what DFSORT can do in one step.
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Wed Jun 08, 2011 10:18 pm    Post subject:
Reply with quote

gylbharat wrote:
Hi Skolusu, I ran this job but got the below error


gylbharat,

I know you are eager to learn and experiment every possible solution, but you need to understand that you are using syncsort which is a competitor product for DFSORT and not all features of DFSORT are supported by syncsort. 99.99% of the time , I do test my solutions before I post them. So if you are getting an error , then you need to realize that it is your sort product that has the problem and not the solution provided.

I'm a DFSORT developer. I'm happy to answer questions on DFSORT and DFSORT's ICETOOL, but I don't answer questions on syncsort.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Thu Jun 09, 2011 12:53 pm    Post subject:
Reply with quote

Thanks Skolusu
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
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm
No new posts Get Record count in summary record fo... Atul Banke DFSORT/ICETOOL 21 Fri Sep 23, 2016 4:17 pm
No new posts Update the Sortout file with record c... karthik_sripal SYNCSORT 8 Tue May 17, 2016 8:52 pm
No new posts CQM : Exec count is 0 but getpages &g... soundarr DB2 1 Thu Mar 17, 2016 10:08 pm
No new posts JCL SORT to split the file and put re... Bill Woodger DFSORT/ICETOOL 17 Wed Feb 17, 2016 4:17 pm


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