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

inter record processing


IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
saravanakmr.tpr

New User


Joined: 16 Feb 2012
Posts: 9
Location: India

PostPosted: Thu Aug 16, 2012 7:40 pm
Reply with quote

Hi,
I need to process a file which has below format

akey1 bkey1 A adate1
akey1 bkey1 A adate2
akey1 bkey2 A adate1
akey1 bkey2 A adate2
akey1 bkey3 A adate3
akey2 bkey1 A adate1
akey3 bkey1 A adate1
akey3 bkey2 A adate2
akey1 bkey1 D ddate1
akey1 bkey1 D ddate2
akey1 bkey1 D ddate3
akey4 bkey1 D ddate1
akey2 bkey1 D ddate1
......

Now i need the output file to be in the below format

akey1 bkey1 A adate1 akey1 bkey1 D ddate1
akey1 bkey1 A adate2 akey1 bkey1 D ddate2
akey1 bkey1 A adate1 akey1 bkey1 D ddate3
akey2 bkey1 A adate1 akey2 bkey1 D ddate1
........

Under the following conditions this reformatting has to be done:
1: Delete date(ddate) against any Add date(adate) in the output record must be greater than add date.

2: There can be no ddate or adate for a particular key. In that case only the adate rec or ddate rec whichever exists has to be written in the output file.

3:There can be any number of adates and ddates for a particular key

4: I can use only SYNCSORT not DFSORT

Can anyone pls help me to resolve this..?
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1787
Location: Bloomington, IL

PostPosted: Thu Aug 16, 2012 7:50 pm
Reply with quote

saravanakmr.tpr wrote:
Hi,
I need to process a file which has below format

akey1 bkey1 A adate1
akey1 bkey1 A adate2
akey1 bkey2 A adate1
akey1 bkey2 A adate2
akey1 bkey3 A adate3
akey2 bkey1 A adate1
akey3 bkey1 A adate1
akey3 bkey2 A adate2
akey1 bkey1 D ddate1
akey1 bkey1 D ddate2
akey1 bkey1 D ddate3
akey4 bkey1 D ddate1
akey2 bkey1 D ddate1
......

Now i need the output file to be in the below format

akey1 bkey1 A adate1 akey1 bkey1 D ddate1
akey1 bkey1 A adate2 akey1 bkey1 D ddate2
akey1 bkey1 A adate1 akey1 bkey1 D ddate3
akey2 bkey1 A adate1 akey2 bkey1 D ddate1
........

Under the following conditions this reformatting has to be done:
1: Delete date(ddate) against any Add date(adate) in the output record must be greater than add date.

2: There can be no ddate or adate for a particular key. In that case only the adate rec or ddate rec whichever exists has to be written in the output file.

3:There can be any number of adates and ddates for a particular key

4: I can use only SYNCSORT not DFSORT

5. I can only post in the DFSORT forum, not the Syncsort/JCL forum.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Thu Aug 16, 2012 8:14 pm
Reply with quote

Hello and welcome to the forum,

Suggest you may get more/better replies if you show something that looks like the real data - both input and output.

What are the recfm and lrecl for the files?
Back to top
View user's profile Send private message
saravanakmr.tpr

New User


Joined: 16 Feb 2012
Posts: 9
Location: India

PostPosted: Thu Aug 16, 2012 8:28 pm
Reply with quote

following is the input:

Code:
3CRIMSCKAN        EXT TEMPLATE 4 1302010-05-15-04.42.36.485629D
3CRIMSCKAN        EXT TEMPLATE 4 1302009-07-07-01.21.50.519386A
3CRIMSCKAN        EXT TEMPLATE 4 1302009-07-07-01.21.50.519386A
3CRNPNEROS        PASTEST2       3502010-08-05-17.05.10.928641D
3CRNPNEROS        PASTEST2       3502010-02-12-14.48.01.261729A
3CRNPNEROS        PASTEST2       3502010-02-12-14.48.01.261729A
3CRNPNEROS        PASTEST2       3502010-08-05-17.05.10.928641D
3CRNPNEROS        PASTEST2       3502010-02-12-14.48.01.261729A
3CRNPNEROS        PASTEST2       3502010-08-05-17.05.10.928641D
3CRNPNEROS        PASTEST2       3502010-08-05-17.05.10.928641D
"Code'd"

RECFM: FB, LRECL: 63
this file has 4 key fields,
key1- 1,3
key2- 4,15
key3- 19,15
key4- 34,3
the order shuld be just as i have written.
output file should be
3CRIMSCKAN EXT TEMPLATE 4 1302009-07-07-01.21.50.519386A3CRIMSCKAN EXT TEMPLATE 4 1302010-05-15-04.42.36.485629D

(del date is greateer than add date)
and do consider the otr conditions also that i mentioned earlier.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Thu Aug 16, 2012 8:41 pm
Reply with quote

Hello,

Please provide a more clear explanation.

I do not understand how the individual data items relate to the others of the same "key".

Looking at the data i believe i can see the pattern, but how should the "rule" be defined? Imagine this was code - how would you document what to do?

What should happen when there are (say 20) items for a key to be selected?
Back to top
View user's profile Send private message
saravanakmr.tpr

New User


Joined: 16 Feb 2012
Posts: 9
Location: India

PostPosted: Fri Aug 17, 2012 6:16 pm
Reply with quote

For the same user I have many add dates and delete dates.
like one user got added in the month 07/2012 and got deleted in 08/2012
and the same user has got added again in the month 09/2012 got deleted in 10/2012. in that case output should have 2 records

userid adddate(07/2012) deldate(08/2012)
userid adddate(09/2012) deldate(10/2012)

Also i may not have add dates or del dates for a particular user
in that case i should write that record as it is

userid adddate(or)deldate <whatever the input file has>

If i have uneven number of add and del dates for a user like 2 add dates (01/2012), (5/2012) and 3 del dates (11/2011), (02/2012), (6/2012)
then the out should be like
userid 11/2012 D
userid 01/2012 A 02/2012 D
userid 05/2012 A 06/2012 D
and der can be 3 add dates and 2 del dates also.

Hope, u can get a clear idea now..
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Fri Aug 17, 2012 7:25 pm
Reply with quote

Hello,

Quote:
Hope, u can get a clear idea now..

I believe i can see the pattern, but you have not explained the rule of how to "tie" groups of records for the same id together. You have explained what you want as output, but not how to deal with the records to get the output.

Looking at the "input" you "know" how the output should be, but we need to have this expressed in processing terms rather than only the output.
Back to top
View user's profile Send private message
saravanakmr.tpr

New User


Joined: 16 Feb 2012
Posts: 9
Location: India

PostPosted: Wed Aug 22, 2012 2:04 pm
Reply with quote

Quote:
rule of how to "tie" groups of records for the same id together

I think I have already explained how to deal with the records. Anyway i'l try to give you some more points which i think will be helpful.
Now u r asking, if der are say 4 recs for the same key how to process those right..?
One of the way that i can think of is like splitting the Add date recs and del date recs into two files and joining them in the next step and removing the unwanted recs. But in this method the resulting no. of recs is the cartesian product of the no. recs in the two files. In that I faced the following problem

userid 2011/08/11 A 2011/09/15 D
userid 2012/05/21 A 2011/09/15 D
userid 2011/08/11 A 2012/07/09 D
userid 2012/05/21 A 2012/07/09 D

in the above set actually the desired output is only the last and first rec. other two is the result of joinin(cartesian product). In the other two i can easily eliminate the one in which add date is greater than del date(2nd rec). but I'm not able to find the 3rd one exactly and remove it.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Wed Aug 22, 2012 7:49 pm
Reply with quote

Hello,

Quote:
I think I have already explained how to deal with the records.
You have explained this in a way that You understand. Unfortunately, to get help you need to explain it so that everyone else can understand. . .

Suggest you post the logical steps you want used to process the input so the desired output will be created.

Why have you not been consistent and used the sample input data records for the later "examples"?

It will be helpful if in your reply you expand the sample input (to show more of the cases that might occur), show the desired output from the expanded input, and then the "ruloes" for processing in sentence form.

When the new info is posted, i'll go back and remove most of the replies that got to this point as i believe they will not be useful ondce we have a complete request.
Back to top
View user's profile Send private message
saravanakmr.tpr

New User


Joined: 16 Feb 2012
Posts: 9
Location: India

PostPosted: Mon Aug 27, 2012 3:05 pm
Reply with quote

ok.
1. I have the Add dates in the first file. and it looks like..
Code:

AACPAACSDEV       780A2012-01-30-11.24.51.226137
AB1PAB1AKLU       780A2011-10-26-14.55.33.456229
AB1PAB1CDEB       780A2011-10-26-11.04.02.554112
AB1PAB1JGON       780A2012-01-12-10.16.22.967238
AB1PAB1KGLY       780A2011-12-15-14.19.44.503711
AB1PAB1AKIN       780A2012-05-10-10.24.43.268403
AB1PAB1BROD       780A2012-02-03-16.07.14.565492
AB1PAB1CNAU       780A2012-01-05-09.26.40.820278
AB1PAB1DCHI       780A2012-03-07-09.01.55.641483
AB1PAB1DDAI       780A2012-03-19-11.42.41.474049
AB1PAB1DELU       780A2012-01-25-15.38.54.469958
AB1PAB1DNOV       780A2011-10-26-13.01.20.396750
AB1PAB1DSUL       780A2011-09-12-10.39.33.322986
AB1PAB1EBOY       780A2012-06-22-10.10.02.222185
AB1PAB1GHAI       780A2012-04-20-15.18.43.605846
AB1PAB1LNEL       780A2012-06-21-12.22.31.512531
AB1PAB1MMEH       780A2012-03-19-11.46.25.793824
AB1PAB1MMUT       780A2012-01-11-10.54.40.557388
AB1PAB1PSAU       780A2012-01-05-11.30.36.191129

2. In the second file I have delete dates
Code:

AACPAACFDIG       780D2011-09-14-11.12.10.149155
AACPAACSISO       780D2012-06-23-04.45.29.278399
AACPAACTIPA       780D2012-05-26-04.49.52.808873
ABAPABACSTO       782D2011-11-19-05.39.09.580214
ABAPABATLAH       782D2011-07-16-04.39.05.943715
AB1PAB1ACOL       780D2011-09-22-11.37.30.342719
AB1PAB1AJOL       780D2012-07-24-08.36.00.557590
AB1PAB1AKLU       780D2011-10-26-14.55.33.456229
AB1PAB1AKLU       780D2011-10-25-11.30.53.997235
AB1PAB1AMAR       780D2012-06-02-04.42.42.594714
AB1PAB1APOL       780D2011-08-12-16.12.38.114839
AB1PAB1APOL       780D2011-11-23-10.37.35.469199
AB1PAB1ASCH       782D2011-09-26-10.13.43.994270
AB1PAB1BARR       780D2011-12-21-10.31.23.814329
AB1PAB1BCHA       780D2012-03-16-10.25.38.280788
AB1PAB1BROW       780D2012-02-18-05.23.13.030652
AB1PAB1BSIA       780D2011-08-06-04.41.10.443595
AB1PAB1BSIA       780D2012-07-11-16.49.53.113952
AB1PAB1CCIA       780D2011-12-21-10.44.07.164793

3. From col 1 to 21 is the key field, at 22nd i have add/del indicator followed by the timestamp.
4. In the first step I do a join based on the key field and get 3 kinds of output datasets
(i) It has matched records from add date and del date files and the o/p looks like...
Code:

AB1PAB1ACOL       780A2011-09-22-11.37.30.342719 D2011-09-22-11.37.30.34
AB1PAB1AKLU       780A2011-10-26-14.55.33.456229 D2011-10-26-14.55.33.45
AB1PAB1AKLU       780A2011-10-26-14.55.33.456229 D2011-10-25-11.30.53.99
AB1PAB1BCHA       780A2012-03-16-10.25.38.280788 D2012-03-16-10.25.38.28
AB1PAB1CDEB       780A2011-10-26-11.04.02.554112 D2011-10-26-11.04.02.55
AB1PAB1CDEL       780A2011-10-20-15.55.09.216339 D2011-08-30-17.50.54.46
AB1PAB1CDEL       780A2011-10-20-15.55.09.216339 D2011-10-20-15.55.09.21
AB1PAB1DBOB       780A2011-11-15-09.46.21.906975 D2011-11-15-09.46.21.90
AB1PAB1DBRU       780A2011-08-10-09.25.47.987758 D2011-08-10-09.25.47.98
AB1PAB1DDAI       780A2012-03-19-11.42.41.474049 D2012-03-19-11.33.48.82
AB1PAB1FRAN       780A2012-06-20-08.59.51.368371 D2012-06-20-08.59.51.36
AB1PAB1GPOR       780A2012-06-14-09.42.48.358314 D2012-06-14-09.42.48.35
AB1PAB1JCHA       780A2011-11-15-10.04.07.148642 D2011-11-15-10.04.07.14
AB1PAB1JGAM       780A2011-12-07-10.52.50.578546 D2011-12-07-10.52.50.57
AB1PAB1JGON       780A2012-01-12-10.16.22.967238 D2012-01-12-10.16.22.96
AB1PAB1JMAR       782A2012-06-12-10.22.07.624950 D2012-06-12-10.22.07.62
AB1PAB1KGLY       780A2011-12-15-14.19.44.503711 D2011-12-15-14.19.44.50
AB1PAB1KKUR       780A2011-09-01-15.03.19.838750 D2011-09-01-15.03.19.83
AB1PAB1LNEL       780A2012-06-21-12.22.31.512531 D2011-07-30-04.43.14.39

(ii) In the second file I have unmatched recs from add dates file alone.
Code:

AACPAACSDEV       780A2012-01-30-11.24.51.226137
ABAPABABHIL       782A2012-05-02-08.35.25.767261
ABAPABAHCOF       782A2012-01-04-14.40.08.626963
ABAPABAMCAR       782A2011-10-31-10.13.32.932685
AB1PAB1ABAB       782A2012-02-27-08.57.04.490770
AB1PAB1AKIN       780A2012-05-10-10.24.43.268403
AB1PAB1AMAN       780A2012-01-26-09.05.21.129123
AB1PAB1BROD       780A2012-02-03-16.07.14.565492
AB1PAB1CNAU       780A2012-01-05-09.26.40.820278
AB1PAB1DCHI       780A2012-03-07-09.01.55.641483
AB1PAB1DELU       780A2012-01-25-15.38.54.469958
AB1PAB1DNOV       780A2011-10-26-13.01.20.396750
AB1PAB1DSUL       780A2011-09-12-10.39.33.322986
AB1PAB1EBOY       780A2012-06-22-10.10.02.222185
AB1PAB1GHAI       780A2012-04-20-15.18.43.605846
AB1PAB1HMUT       782A2012-05-10-09.53.00.576094
AB1PAB1JCLA       780A2011-09-12-10.28.13.448350
AB1PAB1KWIL       782A2011-11-07-15.09.16.374519
AB1PAB1LMCL       780A2012-07-20-09.42.53.405271

(iii) In the 3rd file unmatched recs from del dates file alone.
Code:

AACPAACFDIG       780D2011-09-14-11.12.10.149155
AACPAACSISO       780D2012-06-23-04.45.29.278399
AACPAACTIPA       780D2012-05-26-04.49.52.808873
ABAPABACSTO       782D2011-11-19-05.39.09.580214
ABAPABATLAH       782D2011-07-16-04.39.05.943715
AB1PAB1AJOL       780D2012-07-24-08.36.00.557590
AB1PAB1AMAR       780D2012-06-02-04.42.42.594714
AB1PAB1APOL       780D2011-08-12-16.12.38.114839
AB1PAB1APOL       780D2011-11-23-10.37.35.469199
AB1PAB1ASCH       782D2011-09-26-10.13.43.994270
AB1PAB1BARR       780D2011-12-21-10.31.23.814329
AB1PAB1BROW       780D2012-02-18-05.23.13.030652
AB1PAB1BSIA       780D2011-08-06-04.41.10.443595
AB1PAB1BSIA       780D2012-07-11-16.49.53.113952
AB1PAB1CCIA       780D2011-12-21-10.44.07.164793
AB1PAB1CCIA       780D2011-10-20-15.58.27.981376
AB1PAB1COOK       780D2011-10-08-04.40.22.549513
AB1PAB1CWHE       780D2012-03-27-11.01.13.433127
AB1PAB1DBLI       780D2011-12-06-16.27.40.728724

4. Now i have no issues with 2nd and 3rd file i can directly write recs from these to an o/p file.
5. In the first file only I have a problem. Now I'll tel what exactly the o/p should be. From the two i/p files that I have I need to match the recs based on the key field then I have to analyze the dates that I have got put against each other.
(i) consider this key 'AB1PAB1AKLU 780', I have only one entry in add file for this and two entry in del file while joining i'm getting the o/p as
Code:

AB1PAB1AKLU       780A2011-10-26-14.55.33.456229 D2011-10-26-14.55.33.45
AB1PAB1AKLU       780A2011-10-26-14.55.33.456229 D2011-10-25-11.30.53.99

in this i have to retain only the 1st rec and eliminate the 2nd since the add date is greater than the del date. This may be I can accomplish by doing a simple comparison.
(ii) But this does not work if there are 2 add as well as 2 del date recs.
For the key field '3CRPAADDLEW 130', I have two add dates and 2 del dates while joinin them i get the below o/p
Code:

3CRPAADDLEW 130 2009-07-07-01.21.50.519386A2009-11-28-04.40.11.941057D
3CRPAADDLEW 130 2009-07-07-01.21.50.519386A2011-03-09-10.24.13.221239D
3CRPAADDLEW 130 2010-02-11-11.47.24.574242A2009-11-28-04.40.11.941057D
3CRPAADDLEW 130 2010-02-11-11.47.24.574242A2011-03-09-10.24.13.221239D

Now the required o/p is 1st and last recs. As i said in the previous step I can easily remove the 3rd rec. But how can I eliminate the 2nd rec?? and consider the same scenario for more than 2 add and del dates also.
Looking forward for your reply...
Back to top
View user's profile Send private message
saravanakmr.tpr

New User


Joined: 16 Feb 2012
Posts: 9
Location: India

PostPosted: Wed Sep 05, 2012 8:35 pm
Reply with quote

Any replies... pls....!!!
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Wed Sep 05, 2012 8:41 pm
Reply with quote

Hello,

As no one has proposed sort control statements to accomplish this, it may be time to consider actually writing code in whatever language is supported by your environment.
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania

PostPosted: Wed Sep 05, 2012 8:55 pm
Reply with quote

Quote:
Any replies... pls....!!!


This is very complex problem.

Far too complex for me or anyone else to do on our own time for free.

This is why you have not received any replies.

Requesting replies again is completely out of line and will draw the rath of many seniors on this forum.

I will however be willing to provide you a solution for the appropriate fee.

I work cheaper than most on this forum, and accept Paypal for payment.

Please PM me for further information.
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 -> JCL & VSAM

 


Similar Topics
Topic Forum Replies
No new posts Copy only TEXT or String from a record SYNCSORT 4
No new posts combine multiple unique records into ... DFSORT/ICETOOL 2
No new posts SORT on detail record, then repeat he... DFSORT/ICETOOL 3
No new posts how to complete the end of a record w... DFSORT/ICETOOL 2
No new posts Sort based on the record type DFSORT/ICETOOL 1
Search our Forums:

Back to Top