IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

How to compute Average using Syncsort
IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
afroz.alam

New User


Joined: 15 Mar 2006
Posts: 4
PostPosted: Thu Sep 15, 2011 8:02 am
 Reply with quote
I have a flat file like shown below:
Code:
Item_Desc                 Order_ID              Quantity
--------------------------------------------------------------
Sofa      Or1      75
Sofa      Or2      100
      
Chair      Or3      90
Chair      Or4      100
      
Table      Or5      67
      
Sheet      Or6      78
      
Blanket      Or7      90
      
Pillow      Or8      60
Pillow      Or9      100

I want to create an output file from this input file where the rows need to be sorted after computing the average in case of Item_Desc occuring more than once. For items occuring only once, average quantity = quantity.

Basically, I want output for this case as follows :
Code:
Item Desc                 Order_ID             Quantity
-----------------------------------------------------------
Chair      Or3      80
Chair      Or4      100

Blanket      Or7      90

Pillow      Or8      60
Pillow      Or9      100

Sheet      Or6      78

Sofa      Or1      75
Sofa      Or2      100
      
Table      Or5      67


Please suggest a solution for this problem using either ICETOOL or SORT.

Thanks
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2454
Location: Hampshire, UK
PostPosted: Thu Sep 15, 2011 10:06 am
 Reply with quote
After 5 years of reading the forum you still do not use code tags or provide the basic information asked for in almost every sort topic.
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10888
Location: italy
PostPosted: Thu Sep 15, 2011 11:44 am
 Reply with quote
I edited the post for the code tags, but H311 will freeze before I waste time reformatting icon_biggrin.gif
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
PostPosted: Thu Sep 15, 2011 11:50 am
 Reply with quote
AVG
specifies that an edited or converted average, for the values of a numeric input field in all data records of the report, is to appear in the report record. The average (or mean) is calculated by dividing the total by the count and rounding down to the nearest integer. For example:

10:'Average revenue: ',AVG=(25,5,PD,M5))
Back to top
View user's profile Send private message
afroz.alam

New User


Joined: 15 Mar 2006
Posts: 4
PostPosted: Thu Sep 15, 2011 6:16 pm
 Reply with quote
I apologize for the clumsy representation of information.
However, gylbharat, can you please help me with a complete control card of the AVG computation you mentioned below. Please let me know if you need more information on the problem I posted.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Thu Sep 15, 2011 10:07 pm
 Reply with quote
afroz.alam,

Your output does not match the specifications. You say you wanted to sort the records by descending average. so how did the PILLOW with an average of 80 (100+60 =160/2 =80) get sorted before SOFA with an average of 87.5 (75+100 =175/2 = 87.5) ?? I am guessing that they are typos. Here is DFSORT JCL which will give you the desired results.

Assumptions :

LRECL = 80
RECFM = FB
Item_Desc = 15 bytes starts at pos 1
Order_ID = 10 bytes starts at pos 16
Quantity = 05 bytes starts at pos 26 with leading zeroes supressed.

Use the same input file for both DD's INA and INB.

Code:

//STEP0100 EXEC PGM=SORT           
//SYSOUT   DD SYSOUT=*             
//INA      DD *                   
----+----1----+----2----+----3----+
SOFA           OR1          75     
SOFA           OR2         100     
CHAIR          OR3          90     
CHAIR          OR4         100     
TABLE          OR5          67     
SHEET          OR6          78     
BLANKET        OR7          90     
PILLOW         OR8          60     
PILLOW         OR9         100     
//INB      DD *                   
SOFA           OR1          75     
SOFA           OR2         100     
CHAIR          OR3          90     
CHAIR          OR4         100     
TABLE          OR5          67     
SHEET          OR6          78     
BLANKET        OR7          90     
PILLOW         OR8          60     
PILLOW         OR9         100     
//SORTOUT  DD SYSOUT=*             
//SYSIN    DD *                                                   
  JOINKEYS F1=INA,FIELDS=(01,15,A)                                 
  JOINKEYS F2=INB,FIELDS=(01,15,A)                                 
  REFORMAT FIELDS=(F1:1,80,F2:16,9)                               
  INREC OVERLAY=(91:(81,5,ZD,MUL,+100),DIV,87,3,ZD,EDIT=(TTTTT.TT))
  SORT FIELDS=(91,8,CH,D,1,15,CH,A),EQUALS                         
  OUTREC BUILD=(1,80)                                             
//*
//JNF2CNTL DD *                                                   
  INREC BUILD=(1,15,26,5,UFF,M11,LENGTH=5,C' 001')                 
  SUM FIELDS=(16,5,22,3),FORMAT=ZD                                 
//*


The output from this is

Code:

----+----1----+----2----+----3----+
CHAIR          OR3          90
CHAIR          OR4         100
BLANKET        OR7          90
SOFA           OR1          75
SOFA           OR2         100
PILLOW         OR8          60
PILLOW         OR9         100
SHEET          OR6          78
TABLE          OR5          67
Back to top
View user's profile Send private message
afroz.alam

New User


Joined: 15 Mar 2006
Posts: 4
PostPosted: Thu Sep 15, 2011 11:57 pm
 Reply with quote
Hi Skolusu,
When I copy your code and try to run at my shop i am getting an error -
Am i missing anything?
Code:
000004 //STEP0100 EXEC PGM=SORT,PARM='NOLIST'                                 
000005 //SYSOUT   DD SYSOUT=*                                                 
000006 //INA      DD *                                                         
000007  SOFA           OR1          75                                         
000008  SOFA           OR2         100                                         
000009  CHAIR          OR3          90                                         
000010  CHAIR          OR4         100                                         
000011  TABLE          OR5          67                                         
000012  SHEET          OR6          78                                         
000013  BLANKET        OR7          90                                         
000014  PILLOW         OR8          60                                         
000015  PILLOW         OR9         100                                         
000016 //INB      DD *                                                         
000017  SOFA           OR1          75                                         
000018  SOFA           OR2         100                                         
000019  CHAIR          OR3          90                                         
000020  CHAIR          OR4         100                                         
000021  TABLE          OR5          67                                         
000022  SHEET          OR6          78                                         
000023  BLANKET        OR7          90                                         
000024  PILLOW         OR8          60                                         
000025  PILLOW         OR9         100                                         
000026 //SORTOUT  DD SYSOUT=*                                                 
000027 //SYSIN    DD *                                                         
000028  JOINKEYS F1=INA,FIELDS=(01,15,A)                                       
000029  JOINKEYS F2=INB,FIELDS=(01,15,A)                                       
000030  REFORMAT FIELDS=(F1:1,80,F2:16,9)                                     
000031  INREC OVERLAY=(91:(81,5,ZD,MUL,+100),DIV,87,3,ZD,EDIT=(TTTTT.TT))     
000032  SORT FIELDS=(91,8,CH,D,1,15,CH,A),EQUALS                               
000033  OUTREC BUILD=(1,80)                                                   
000034 //JNF2CNTL DD *                                                         
000035  INREC BUILD=(1,15,26,5,UFF,M11,LENGTH=5,C' 001')                       
000036  SUM FIELDS=(16,5,22,3),FORMAT=ZD                                       
000037 //*                                                                     
==========================
Error Msg:
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           
******************************* Bottom of Data **********
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada
PostPosted: Fri Sep 16, 2011 12:03 am
 Reply with quote
You are using SYNCSORT not DFSORT.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
PostPosted: Fri Sep 16, 2011 1:15 am
 Reply with quote
Hello,

You need to post the page(s) with the diagnostic informaton - from the top to the bottom.
Back to top
View user's profile Send private message
afroz.alam

New User


Joined: 15 Mar 2006
Posts: 4
PostPosted: Fri Sep 16, 2011 2:12 am
 Reply with quote
Quote:
You are using SYNCSORT not DFSORT.

I am not sure if our shop has or supports DFSORT. Can you please provide control card supported by SYNCSORT?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
PostPosted: Fri Sep 16, 2011 2:20 am
 Reply with quote
there was a thread just the other day, where someone was using syncsort with joinkeys. syncsort has other syntax or can't use jfn1cntl or refomat or something.

i use dfsort so didnot bother to remember,
but, looking thru posts for the last couple of days will provide the answer,
since the ts obviously does not feel compelled to consult a manual.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
PostPosted: Fri Sep 16, 2011 3:00 am
 Reply with quote
Hello,

Here's a hint. . . Getting late here and i've one quite similar. . .

JOINKEYS FILE=F1,FIELDS=(01,15,A)
JOINKEYS FILE=F2,FIELDS=(01,15,A)
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> JCL & VSAM

 

Similar Topics
Topic Forum Replies
No new posts Syncsort help SYNCSORT 3
No new posts SYNCSORT-DB2 interface SYNCSORT 0
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Timestamp difference and its average ... DB2 11
No new posts Count Records with a crietaria in a f... DFSORT/ICETOOL 5
Search our Forums:

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