Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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 sort records based on length exceeds ... maxsubrat DFSORT/ICETOOL 7 Wed Oct 04, 2017 4:48 pm
No new posts JOINKEYS Paired and Unpaired Sort MFwhiz DFSORT/ICETOOL 4 Fri Sep 29, 2017 1:48 am
No new posts Difference in SORT & ICETOOL Mohan Kothakota DFSORT/ICETOOL 5 Fri Sep 22, 2017 4:56 pm
No new posts OC4 in SORT Mohan Kothakota DFSORT/ICETOOL 2 Tue Sep 12, 2017 6:30 pm
No new posts SORT Trailer Count - LRECL Output co... amorante DFSORT/ICETOOL 5 Tue Aug 29, 2017 8:57 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us