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
 

 

How to compute Average using Syncsort

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
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    Post subject: How to compute Average using Syncsort
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: 1715
Location: UK

PostPosted: Thu Sep 15, 2011 10:06 am    Post subject:
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Thu Sep 15, 2011 11:44 am    Post subject: Reply to: How to compute Average using SORT
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    Post subject:
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    Post subject: Reply to: How to compute Average using SORT
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    Post subject:
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    Post subject: Reply to: How to compute Average using SORT
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: 641
Location: Whitby, ON, Canada

PostPosted: Fri Sep 16, 2011 12:03 am    Post subject:
Reply with quote

You are using SYNCSORT not DFSORT.
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: Fri Sep 16, 2011 1:15 am    Post subject:
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    Post subject: Reply to: How to compute Average using Syncsort
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    Post subject:
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

Site Director


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

PostPosted: Fri Sep 16, 2011 3:00 am    Post subject:
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    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 syncsort: copy lines after the keyword shreya19 SYNCSORT 7 Fri Dec 02, 2016 9:47 am
No new posts Syncsort - Hard coded Value in output PORYES SYNCSORT 1 Thu Nov 03, 2016 9:23 am
No new posts Syncsort - NULL in Integer field chec... nartcr SYNCSORT 4 Thu Oct 06, 2016 6:47 am
No new posts ICETOOL/SYNCSORT - SYSPRINT output nartcr DFSORT/ICETOOL 17 Wed Oct 05, 2016 10:46 pm
No new posts Syncsort version supporting FINDREP c... juares castro SYNCSORT 9 Tue Aug 09, 2016 6:54 pm


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