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
 

 

Grouping records by a date

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

New User


Joined: 19 Nov 2008
Posts: 90
Location: Spain

PostPosted: Thu Aug 23, 2012 2:24 pm    Post subject: Grouping records by a date
Reply with quote

Hello. It's been a while since my last time at the forums.

We have a requirement to set up a file to load a partitioned table. Let's asume an Input record of 20 bytes length, and FB format.

The file records are as follow:
Positions 1 trhough 10, A Date, in this format: aaaa-mm-dd
Positions 11 through 20, some text

The requirement is to number records in groups by the year and month of the date. Usually, the input records only have three months in date expand, but it could be more or less. The desired output should be a sequential number for each different year-month occurrence. Here is an example of the input records:

Code:
2012-05-01AAAAAAAAAA
2012-05-02AAAAAAAAAA
2012-05-03AAAAAAAAAA
2012-04-01AAAAAAAAAA
2012-06-10AAAAAAAAAA
2012-04-11AAAAAAAAAA
2012-06-12AAAAAAAAAA
2012-06-13AAAAAAAAAA
2012-06-14AAAAAAAAAA
2012-04-15AAAAAAAAAA
2012-04-16AAAAAAAAAA
2012-05-17AAAAAAAAAA


And here is an example of the desired output:

Code:
2012-04-01AAAAAAAAAA01
2012-04-11AAAAAAAAAA01
2012-04-15AAAAAAAAAA01
2012-04-16AAAAAAAAAA01
2012-05-01AAAAAAAAAA02
2012-05-02AAAAAAAAAA02
2012-05-03AAAAAAAAAA02
2012-05-17AAAAAAAAAA02
2012-06-10AAAAAAAAAA03
2012-06-12AAAAAAAAAA03
2012-06-13AAAAAAAAAA03
2012-06-14AAAAAAAAAA03


Can this be accomplished with a sort step? We have looked at the When=Group option, but that requires beforehand that you know a key in order to differentiate different groups of records, however, the dates could be random, and there could be even months without info. Se we are evaluating if we should write a Cobol program for this or attempt a Sort step

Thanks a lot in advance

Best regards!

Oliver
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7315

PostPosted: Thu Aug 23, 2012 2:42 pm    Post subject: Reply to: Grouping records by a date
Reply with quote

Good to see you back - for newcomers who want to know about the sig, do a search, it has been discussed before :-)

Can you show the level of DFSORT you have? - please post the ICE201I message from any sort step.

If you've looked at WHEN=GROUP, did you notice KEYBEGIN? If you have that, it is easy.

Without KEYBEGIN, you can append a sequence number, with RESTART=(1,10) and then do the WHEN=GROUP with BEGIN= the value 1 in the appended sequence number position.
Back to top
View user's profile Send private message
Gabriel Araujo Alves

New User


Joined: 20 Jul 2010
Posts: 38
Location: Brazil

PostPosted: Thu Aug 23, 2012 7:14 pm    Post subject:
Reply with quote

like this???

Code:

//*********************************************************************
//STEP1       EXEC PGM=SORT                                             
//SYSOUT      DD SYSOUT=*                                               
//SYSPRINT    DD SYSOUT=*                                               
//SORTIN      DD *                                                     
2012-05-01AAAAAAAAAA                                                   
2012-05-02AAAAAAAAAA                                                   
2012-05-03AAAAAAAAAA                                                   
2012-04-01AAAAAAAAAA                                                   
2012-06-10AAAAAAAAAA                                                   
2012-04-11AAAAAAAAAA                                                   
2012-06-12AAAAAAAAAA                                                   
2012-06-13AAAAAAAAAA                                                   
2012-06-14AAAAAAAAAA                                                   
2012-04-15AAAAAAAAAA                                                   
2012-04-16AAAAAAAAAA                                                   
2012-05-17AAAAAAAAAA                                                   
//SORTOUT     DD SYSOUT=*                                               
//SYSIN       DD *                                                     
 SORT FIELDS=(01,10,UFF,A)                                             
 OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(25:SEQNUM,03,ZD,RESTART=(01,07))),   
        IFTHEN=(WHEN=GROUP,BEGIN=(25,03,ZD,EQ,1),PUSH=(21:ID=2))       
 OUTFIL BUILD=(01,22)                                                   
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7315

PostPosted: Thu Aug 23, 2012 8:05 pm    Post subject: Reply to: Grouping records by a date
Reply with quote

Ola! Gabriel, mais ou menos.

The UFF on the 1,10 won't affect the sort order, so I'd leave it out.

You can ensure the 22-byte output by using IFOUTLEN=22 rather than the BUILD on OUTFIL (or elsewhere).

You could be more generous with the sequence number and the ID, but since TS didn't give us any clues as to volumes, that's fine.
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Fri Aug 24, 2012 1:41 am    Post subject:
Reply with quote

ojdiaz,

It is quite easy with WHEN=GROUP and keybegin. Use the following DFSORT JCL which will give you the desired results.

Code:

//STEP0100 EXEC PGM=SORT                             
//SYSOUT   DD SYSOUT=*                               
//SORTIN   DD *                                       
----+----1----+----2----+----3----+----4----+----5----
2012-05-01AAAAAAAAAA                                 
2012-05-02AAAAAAAAAA                                 
2012-05-03AAAAAAAAAA                                 
2012-04-01AAAAAAAAAA                                 
2012-06-10AAAAAAAAAA                                 
2012-04-11AAAAAAAAAA                                 
2012-06-12AAAAAAAAAA                                 
2012-06-13AAAAAAAAAA                                 
2012-06-14AAAAAAAAAA                                 
2012-04-15AAAAAAAAAA                                 
2012-04-16AAAAAAAAAA                                 
2012-05-17AAAAAAAAAA                                 
//SORTOUT  DD SYSOUT=*                               
//SYSIN    DD *                                       
  SORT FIELDS=(1,7,CH,A)                             
  OUTREC IFOUTLEN=22,                                 
  IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,7),PUSH=(21:ID=2))   
//*


If you have a version which does NOT support Keybegin , then use the following control cards
Code:

//SYSIN    DD *                                             
  SORT FIELDS=(1,7,CH,A)                                   
  OUTREC IFOUTLEN=22,                                       
  IFTHEN=(WHEN=INIT,OVERLAY=(23:SEQNUM,3,ZD,RESTART=(1,7))),
  IFTHEN=(WHEN=GROUP,BEGIN=(23,3,ZD,EQ,1),PUSH=(21:ID=2))   
//*
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 Aug 24, 2012 7:50 pm    Post subject: Reply to: Grouping records by a date
Reply with quote

Hello,

Gabriel posted the original working solution.

When Skolusu posted the optimized solution, Gabriel tried it on his system and found that KEYBEGIN is not available on that system and provided this info. Not such a great sin?

Methinks we need to lighten up a bit. . . . icon_confused.gif

d
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10274
Location: italy

PostPosted: Fri Aug 24, 2012 7:54 pm    Post subject: Reply to: Grouping records by a date
Reply with quote

my remark was not personally addressed to Gabriel...

it was a ( bit rude I agrre ) comment about the general habit , seen pretty often, of making comments too often irrelevant to the topic in question
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 How to add header with Date(YYMMDD) i... Rajan Moorthy DFSORT/ICETOOL 2 Thu Jul 06, 2017 11:44 pm
No new posts Validate date and numeric fields and ... Rick Silvers DFSORT/ICETOOL 6 Thu May 11, 2017 6:51 pm
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
No new posts LISTIDR compiled date/time jerryte IBM Tools 3 Thu Apr 20, 2017 7:37 pm
No new posts SUBSTR for Date functions Shaheen Shaik DB2 4 Thu Apr 06, 2017 2:14 pm


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