Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref

Author Message
afroz.alam

New User

Joined: 15 Mar 2006
Posts: 4

Posted: Thu Sep 15, 2011 8:02 am    Post subject: How to compute Average using Syncsort

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

Nic Clouston

Global Moderator

Joined: 10 May 2007
Posts: 2188
Location: Hampshire, UK

 Posted: Thu Sep 15, 2011 10:06 am    Post subject: 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.
enrico-sorichetti

Senior Member

Joined: 14 Mar 2007
Posts: 10609
Location: italy

 Posted: Thu Sep 15, 2011 11:44 am    Post subject: Reply to: How to compute Average using SORT I edited the post for the code tags, but H311 will freeze before I waste time reformatting
gylbharat

Active Member

Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

 Posted: Thu Sep 15, 2011 11:50 am    Post subject: 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))
afroz.alam

New User

Joined: 15 Mar 2006
Posts: 4

 Posted: Thu Sep 15, 2011 6:16 pm    Post subject: Reply to: How to compute Average using SORT 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.
Skolusu

Senior Member

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

Posted: Thu Sep 15, 2011 10:07 pm    Post subject:

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
afroz.alam

New User

Joined: 15 Mar 2006
Posts: 4

Posted: Thu Sep 15, 2011 11:57 pm    Post subject: Reply to: How to compute Average using SORT

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 **********
don.leahy

Active Member

Joined: 06 Jul 2010
Posts: 683

 Posted: Fri Sep 16, 2011 12:03 am    Post subject: You are using SYNCSORT not DFSORT.
dick scherrer

Site Director

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

 Posted: Fri Sep 16, 2011 1:15 am    Post subject: Hello, You need to post the page(s) with the diagnostic informaton - from the top to the bottom.
afroz.alam

New User

Joined: 15 Mar 2006
Posts: 4

Posted: Fri Sep 16, 2011 2:12 am    Post subject: Reply to: How to compute Average using Syncsort

 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?
dbzTHEdinosauer

Global Moderator

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

 Posted: Fri Sep 16, 2011 2:20 am    Post subject: 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.
dick scherrer

Site Director

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

 Posted: Fri Sep 16, 2011 3:00 am    Post subject: 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)
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics LRECL in syncsort frozenblood87 SYNCSORT 4 Tue Feb 19, 2019 6:50 am SYNCSORT keep MIN/MAX record Cloink SYNCSORT 16 Mon Sep 17, 2018 3:00 pm Compute statement with Cobol Z/os 5.2 Chetan Kumar COBOL Programming 2 Fri May 11, 2018 8:02 pm Reformatting and adding additional fi... Badbeef SYNCSORT 6 Thu Apr 05, 2018 4:45 pm Split 1 file into 10 output Files - S... Prasanth Kumar SYNCSORT 5 Sat Sep 16, 2017 12:02 am

 © 2003-2017 IBM MAINFRAME Software Support Division
 Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us