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

grouping based on keys


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
sagarnsydney

New User


Joined: 12 Dec 2008
Posts: 16
Location: sydney

PostPosted: Tue Oct 20, 2009 8:43 am
Reply with quote

Hi All,

I have a requirement to know count based on some grouping.

I have 3 fields and I want to get the counts grouping these 3 fields.

For Eg:
Input file has data as below:

XX 0001 AAAAAA
XX 0002 AAAAAA
XX 0003 AAAAAA
XX 0004 AAAAAA
YY 0001 BBBBBB
YY 0002 BBBBBB
YY 0003 BBBBBB
ZZ 0001 CCCCCC
ZZ 0002 CCCCCC
ZZ 0003 CCCCCC

I am able to get the counts as below:
XX AAAAA 0000004
YY BBBBBB 0000003
ZZ CCCCCC 0000003

Means I am able to count by first and third fields i.e. XX and AAAAAA, YY and BBBBBB, ZZ and CCCCCC. But, my requirement bit changed to count based on the middle field also.

I want to group based on the second field but with specific range.

For Eg:
I want to get the counts individually between 1 and 2 inclusive , 3 and 4 inclusive. Means the output shoud be as below:

XX AAAAA 0000002 - for second field value between 1 and 2
XX AAAAA 0000002 - for second field value between 3 and 4
YY BBBBBB 0000002 - for second field value between 1 and 2
YY BBBBBB 0000001 - for second field value between 3 and 4
ZZ CCCCCC 0000002 - for second field value between 1 and 2
ZZ CCCCCC 0000001 - for second field value between 3 and 4

I tried to search in the forum to get this output. But, I couldn't find the result.

Could any one help me please.

Thanks a ton in advance.

Regards,
Sagar
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Tue Oct 20, 2009 10:58 am
Reply with quote

Hi,
I have assumed that input LRECL=14 and output LRECL=17

Code:

//STEP0100 EXEC PGM=SORT         
//SYSOUT   DD SYSOUT=*           
//SORTIN   DD *                 
XX 0001 AAAAAA                   
XX 0002 AAAAAA                   
XX 0003 AAAAAA                   
XX 0004 AAAAAA                   
YY 0001 BBBBBB                   
YY 0002 BBBBBB                   
YY 0003 BBBBBB                   
ZZ 0001 CCCCCC                   
ZZ 0002 CCCCCC                   
ZZ 0003 CCCCCC                   
/*                               
//SORTOUT DD DSN=youroutputdsn
//SYSIN DD *       
  INREC IFTHEN=(WHEN=(4,4,ZD,LE,2),OVERLAY=(15:C'10000001')),   
        IFTHEN=(WHEN=(4,4,ZD,GT,2),OVERLAY=(15:C'20000001'))     
  SORT FIELDS=(1,2,CH,A,9,6,CH,A,15,1,CH,A)                     
  SUM FIELDS=(16,7,ZD)     
  OUTREC BUILD=(1,2,X,9,6,X,16,7)                       
/*                                                               

Output will be
Code:

XX AAAAAA 0000002
XX AAAAAA 0000002
YY BBBBBB 0000002
YY BBBBBB 0000001
ZZ CCCCCC 0000002
ZZ CCCCCC 0000001
Back to top
View user's profile Send private message
sagarnsydney

New User


Joined: 12 Dec 2008
Posts: 16
Location: sydney

PostPosted: Wed Oct 21, 2009 8:04 am
Reply with quote

Hi Sambhaji,

Thank you very much for your prompt response.

Is it possible to add any thing more to the output to identify LESS2 and MORE2 and also can we supress zeros before the counts.

For Eg:
Can I get output as below:

XX AAAAAA LESS2 2
XX AAAAAA MORE2 2
YY BBBBBB LESS2 2
YY BBBBBB MORE2 1
ZZ CCCCCC LESS2 2
ZZ CCCCCC MORE2 1


Once again thank you in advance.

Regards,
Sagar
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Wed Oct 21, 2009 9:48 am
Reply with quote

Needs little Change in sortcard
Code:

//SYSIN DD *     
  INREC IFTHEN=(WHEN=(4,4,ZD,LE,2),OVERLAY=(15:C'LESS210000001')), 
        IFTHEN=(WHEN=(4,4,ZD,GT,2),OVERLAY=(15:C'MORE220000001'))   
  SORT FIELDS=(1,2,CH,A,9,6,CH,A,20,1,CH,A)                         
  SUM FIELDS=(21,7,ZD)                                             
  OUTREC BUILD=(1,2,X,9,6,X,15,5,X,21,7)                           
/*                                                                 
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Wed Oct 21, 2009 10:04 pm
Reply with quote

Quote:
can we supress zeros before the counts


To do that, change Sambhaji's OUTREC statement to:

Code:

  OUTREC BUILD=(1,2,X,9,6,X,15,5,X,21,7,ZD,M10,LENGTH=7)         
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Wed Oct 21, 2009 10:28 pm
Reply with quote

Here's another way to do it with DFSORT:

Code:

//S2 EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*
//DFSMSG   DD SYSOUT=*
//SORTIN   DD DSN=... input file (FB/14)
//SORTOUT  DD DSN=...  output file (FB/23)
//SYSIN    DD *
  INREC IFOUTLEN=23,                                             
        IFTHEN=(WHEN=(4,4,ZD,LE,2),BUILD=(1,3,9,6,11:C'LESS2')), 
        IFTHEN=(WHEN=NONE,BUILD=(1,3,9,6,11:C'MORE2'))           
  SORT FIELDS=(1,15,CH,A)                                         
  OUTFIL REMOVECC,NODETAIL,                                       
   SECTIONS=(1,15,                                               
    TRAILER3=(1,15,X,COUNT=(M10,LENGTH=7)))                       
/*
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 -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts Grouping by multiple headers DFSORT/ICETOOL 7
No new posts Use input file with OMIT rcd keys? DFSORT/ICETOOL 15
No new posts To search DB2 table based on Conditio... DB2 1
This topic is locked: you cannot edit posts or make replies. Merge 2 input files based on the reco... JCL & VSAM 2
No new posts Split large FB file based on Key coun... DFSORT/ICETOOL 4
Search our Forums:

Back to Top