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
 

 

Multi-level SORT and sum

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
JVINAY

New User


Joined: 21 May 2008
Posts: 3
Location: switzerland

PostPosted: Mon May 26, 2008 8:19 pm    Post subject: Multi-level SORT and sum
Reply with quote

Hello,

First of all, here is an example (I think it's better to understand what I'm looking for).


=> Here is the input file :

Code:

EUROPE ; CH ; LAUSANNE ; 0100
EUROPE ; FR ; PARIS    ; 0024
EUROPE ; CH ; ZURICH   ; 0154
ASIA   ; JP ; TOKYO    ; 0004
EUROPE ; CH ; LAUSANNE ; 0200
EUROPE ; CH ; ZURICH   ; 0077
EUROPE ; CH ; GENEVE   ; 0154
EUROPE ; FR ; PARIS    ; 0752
ASIA   ; JP ; TOKYO    ; 0014
EUROPE ; FR ; PARIS    ; 0054
EUROPE ; CH ; GENEVE   ; 0128


And the description is "AREA ; COUNTRY ; CITY ; AMOUNT OF WHAT YOU WANT". This file is in any order, and there could have several records with same threesome "AREA/COUNTRY/CITY".

=> I want an output file orderer by "AREA/COUNTRY/CITY" with sums on QUANTITY and with high-level totals on all and "AREA" and "AREA/COUNTRY". Here is this output file I want (and in this order) :

Code:

ASIA   ; JP ; TOKYO    ; 0018     :TOTAL on AREA/COUNTRY/CITY
ASIA   ; JP ;          ; 0018     :TOTAL on AREA/COUNTRY
ASIA   ;    ;          ; 0018     :TOTAL on AREA
EUROPE ; CH ; GENEVE   ; 0282     :TOTAL on AREA/COUNTRY/CITY
EUROPE ; CH ; LAUSANNE ; 0300     :TOTAL on AREA/COUNTRY/CITY
EUROPE ; CH ; ZURICH   ; 0231     :TOTAL on AREA/COUNTRY/CITY
EUROPE ; CH ;          ; 0813     :TOTAL on AREA/COUNTRY
EUROPE ; FR ; PARIS    ; 0830     :TOTAL on AREA/COUNTRY/CITY
EUROPE ; FR ;          ; 0830     :TOTAL on AREA/COUNTRY
EUROPE ;    ;          ; 1643     :TOTAL on AREA
       ;    ;          ; 1661     :"SUPER" TOTAL


I've no problem to obtain a file with the only totals on AREA/COUNTRY/CITY, but I would know if someone know how to generate a file with high-level totals.

Thanks for your help !
Back to top
View user's profile Send private message

William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Mon May 26, 2008 8:34 pm    Post subject:
Reply with quote

JVINAY,

Does the fixed data accurately reflect what you intended?
Are there really semi-colons in the data?
Do you want the words after the colon included too?

Bill
Back to top
View user's profile Send private message
JVINAY

New User


Joined: 21 May 2008
Posts: 3
Location: switzerland

PostPosted: Mon May 26, 2008 11:19 pm    Post subject:
Reply with quote

Hello,

The text after the caracter ":" was only here to explain my post. I want to know if it's possible to use a multi-level SUM with generation of total line.

Thx
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Tue May 27, 2008 11:27 pm    Post subject:
Reply with quote

Jvinay,


The following DFSORT JCL which will give you the desired results

Code:

//STEP0100 EXEC PGM=ICEMAN                                           
//SYSOUT   DD SYSOUT=*                                               
//SORTIN   DD *                                                     
EUROPE ; CH ; LAUSANNE ; 0100                                       
EUROPE ; FR ; PARIS    ; 0024                                       
EUROPE ; CH ; ZURICH   ; 0154                                       
ASIA   ; JP ; TOKYO    ; 0004                                       
EUROPE ; CH ; LAUSANNE ; 0200                                       
EUROPE ; CH ; ZURICH   ; 0077                                       
EUROPE ; CH ; GENEVE   ; 0154                                       
EUROPE ; FR ; PARIS    ; 0752                                       
ASIA   ; JP ; TOKYO    ; 0014                                       
EUROPE ; FR ; PARIS    ; 0054                                       
EUROPE ; CH ; GENEVE   ; 0128                                       
//OUT1     DD DSN=&&T1,DISP=(,PASS),UNIT=SYSDA,SPACE=(CYL,(1,1),RLSE)
//OUT2     DD DSN=&&T2,DISP=(,PASS),UNIT=SYSDA,SPACE=(CYL,(1,1),RLSE)
//OUT3     DD DSN=&&T3,DISP=(,PASS),UNIT=SYSDA,SPACE=(CYL,(1,1),RLSE)
//OUT4     DD DSN=&&T4,DISP=(,PASS),UNIT=SYSDA,SPACE=(CYL,(1,1),RLSE)
  SORT FIELDS=(01,24,CH,A)                                       
  OUTREC BUILD=(1,30,94:X)                                       
  OUTFIL FNAMES=OUT1,REMOVECC,NODETAIL,                         
  SECTIONS=(1,24,                                               
  TRAILER3=(1,24,TOT=(26,4,ZD,M11,LENGTH=8),                     
            ':TOTAL ON AREA/COUNTRY/CITY',81:1,13,C'1'))         
  OUTFIL FNAMES=OUT2,REMOVECC,NODETAIL,                         
  SECTIONS=(1,13,                                               
  TRAILER3=(1,13,10X,C';',TOT=(26,4,ZD,M11,LENGTH=8),           
             C':TOTAL ON AREA/COUNTRY',81:1,13,C'2'))           
  OUTFIL FNAMES=OUT3,REMOVECC,NODETAIL,                         
  SECTIONS=(1,08,                                               
  TRAILER3=(1,08,4X,C';',10X,C';',TOT=(26,4,ZD,M11,LENGTH=8),   
             C':TOTAL ON AREA',81:1,13,C'3'))                   
  OUTFIL FNAMES=OUT4,REMOVECC,NODETAIL,                         
  TRAILER1=(7X,C';',4X,C';',10X,C';',TOT=(26,4,ZD,M11,LENGTH=8),
             C':"SUPER" TOTAL',81:14C'9')                       
/*                                                               
//STEP0200 EXEC PGM=ICEMAN                                       
//SYSOUT   DD SYSOUT=*                                           
//SORTIN   DD DSN=&&T1,DISP=SHR                                 
//         DD DSN=&&T2,DISP=SHR                                 
//         DD DSN=&&T3,DISP=SHR                                 
//         DD DSN=&&T4,DISP=SHR                                 
//SORTOUT  DD SYSOUT=*                                           
//SYSIN    DD *                                                 
  SORT FIELDS=(81,14,CH,A)                                       
  OUTREC BUILD=(01,80)                                           
/*


Hope this helps...

Cheers
Back to top
View user's profile Send private message
JVINAY

New User


Joined: 21 May 2008
Posts: 3
Location: switzerland

PostPosted: Wed May 28, 2008 9:13 pm    Post subject: Reply to: Multi-level SORT and sum
Reply with quote

Thanks

I'll try it tomorrow
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 -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Innovations in Your Mainframe Sort Arun Raj PC Guides & IT News 4 Wed Mar 15, 2017 10:38 pm
No new posts SORT VSAM file with each field one by... maxsubrat DFSORT/ICETOOL 6 Tue Mar 14, 2017 1:07 pm
No new posts SORT requirement sjiraga COBOL Programming 6 Thu Mar 09, 2017 9:11 pm
No new posts SORT to append Month and YEAR in MMYY... tisamf DFSORT/ICETOOL 1 Wed Mar 08, 2017 4:46 pm
No new posts JCL sort Match and NoMatch rakeshsekar1987 SYNCSORT 2 Thu Feb 02, 2017 10:26 pm


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