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
 

 

Merging multiple records onto single record based on key
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 129
Location: India

PostPosted: Tue Jul 09, 2013 1:35 am    Post subject: Merging multiple records onto single record based on key
Reply with quote

Hi All,

I would like to merge data of 2 records onto a single record
The first 6 bytes of input is the key and based on that the 8th byte, 9th byte,10th byte which comes in multiple lines should come in single record (if key matches)

Code:
Input is

ABCDEF X 
ABCDEF   X
ABCDEF      <b>
MNOPQR X
MNOPQR <b> <b><b>
MNOPQR <b><b><b>
UVWXYZ <b><b><b> 


Code:
Output is

ABCDEF XX<b>
MNOPQR X<b><b>
UVWXYZ<b><b><b>


Whether this is possible using SORT card ?
Please help.

Thanks
Vinu
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Tue Jul 09, 2013 1:48 am    Post subject: Reply to: Merging multiple records onto single record based
Reply with quote

How many keys do you have which could match, just three as you show, or more?

RECFM/LRECL of input file.
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 129
Location: India

PostPosted: Tue Jul 09, 2013 3:37 am    Post subject:
Reply with quote

There are so many records in the input file.
The first 6 byte is considered as key. If the first 6 bytes match for 3 or 4 records, then we ned to do merging as mentioned.

Thanks
Vinu
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Tue Jul 09, 2013 5:01 am    Post subject: Reply to: Merging multiple records onto single record based
Reply with quote

Are you saying that four is the maximum number of records which could have the same key?

Anything else I asked?
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Tue Jul 09, 2013 12:37 pm    Post subject:
Reply with quote

You say 3 or 4 records ........ is there no merge for only two matches ???
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 129
Location: India

PostPosted: Tue Jul 09, 2013 6:19 pm    Post subject:
Reply with quote

The RECL can be 10 bytes with FB
The record with same key will either have 8th byte, or 9th byte filled with 'X'.
So whenever the first 6 bytes match (say for 2 or 3 records), we should be able to merge the column data of 8th byte, 9th byte and 10th byte to a single record. The main file can have 1000s of records. Sorry for using the word "KEY". It should be like "If the first 6 bytes match".

Thanks
Vinu
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7913
Location: Bellevue, IA

PostPosted: Tue Jul 09, 2013 6:36 pm    Post subject:
Reply with quote

Quote:
The RECL can be 10 bytes with FB
This is complete and utter garbage. A record length "can" be nothing -- it WILL be a value and you were asked to provide that value and yet you cannot answer with a simple declarative statement. Furthermore, the examples you posted include as many as 16 bytes for one record, so saying the RECL "can be 10" is an obvious falsehood. For variable length files, LRECL is always a single number -- the length of the longest record plus 4 bytes (for the record descriptor word) and for fixed length files, the LRECL is also a single number -- namely, the length of every record in the file.

I think your first problem is you have no idea what your input data looks like, nor its characteristics.
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Tue Jul 09, 2013 6:42 pm    Post subject:
Reply with quote

vinu78

You really must learn to describe what it is that you want in a clear and concise manner.

Looking again at the original post, there seems to be some rule in there somewhere to cater for the MNOPQR records.

As the X is held over and one of the <b> fields is replaced.

Is this rule only for MNOPQR or for all records. Are there any other ules that we should know about.
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 129
Location: India

PostPosted: Tue Jul 09, 2013 7:45 pm    Post subject:
Reply with quote

Sorry. I am not sure whether I have described wrongly.
Mr.Bill has asked me the LRECL / RECFM and so I have given that.

Let me make it short. Whether we can overlay the column data (just like splicing in ICETOOL) using SYNCSORT utility.

"If the first 6 byte matches, then I would like to get the 9th position and 10th pos overlaid to the base record. "

Thanks
Vinu
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1715
Location: UK

PostPosted: Tue Jul 09, 2013 8:40 pm    Post subject:
Reply with quote

And all the other questions? For example, what is the max/min number of records per 'key'? What if there is more than one record, with the same 'key', having data in, say, column 9?
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: Tue Jul 09, 2013 8:55 pm    Post subject:
Reply with quote

Hello,

As you have been here more than 4 years, we might expect that you would present your question in a way that most of us can understand.

Keep in mind that while you know exactly what you want / need, we might not. A more represebntative set of sample input/output would probably help. The real data probably does not contain only X and <b> entries. More realistic samples will help us help you.

As Nic mentioned, the other questions need to be answered . . .
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 129
Location: India

PostPosted: Tue Jul 09, 2013 9:36 pm    Post subject:
Reply with quote

Nic,

As far as I know, there sill be 3 or 4 records per same key.
There won't be more than 1 record with same key in 8th byte or 9th byte.
Data value 'X' will either be in 8th byte or 9th byte and won't be in 8th byte only for the same set of keys or 9th byte only within same set of keys.

Thanks Dick for your suggestion. I wil follow that.

Thanks
Vinu
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 129
Location: India

PostPosted: Wed Jul 10, 2013 8:12 pm    Post subject:
Reply with quote

Hi All,

Whether OVERLAY method used in SORT card will solve this.

Thanks
Vinu
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1715
Location: UK

PostPosted: Wed Jul 10, 2013 8:24 pm    Post subject:
Reply with quote

Is there a question there? Assuming there is - what happened when you tried it?
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 129
Location: India

PostPosted: Wed Jul 10, 2013 8:44 pm    Post subject:
Reply with quote

I just did the basic syntax of OVERLAY..so was not able o get the desired result.

So can somebody who knows well about it please throw some light on this if we can acheive the same using OVERLAY

Rgds
Vinu
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1715
Location: UK

PostPosted: Wed Jul 10, 2013 8:47 pm    Post subject:
Reply with quote

So try some not-basic syntax. Manual in one hand, keyboard in other. And reference other topics.

And, we cannot tell you how to amend your control cards as yopu have not posted them. We are not mind-readers - at least, not today.
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: Wed Jul 10, 2013 9:12 pm    Post subject:
Reply with quote

Hello,

You need to post the JCL and control statements you used and the informational/diagnostic information generated by the run.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2165
Location: @my desk

PostPosted: Fri Jul 12, 2013 4:00 pm    Post subject:
Reply with quote

dick scherrer wrote:
Keep in mind that while you know exactly what you want / need, we might not. A more represebntative set of sample input/output would probably help. The real data probably does not contain only X and <b> entries. More realistic samples will help us help you.
vinu78 wrote:
Thanks Dick for your suggestion. I wil follow that
It might help you if you start following it right from this topic.
Back to top
View user's profile Send private message
Dale Robertson

New User


Joined: 21 Jun 2013
Posts: 44
Location: U.S.A.

PostPosted: Sat Jul 13, 2013 2:49 am    Post subject: Re: Merging multiple records onto single record based on key
Reply with quote

vinu78 wrote:
Hi All,

I would like to merge data of 2 records onto a single record
The first 6 bytes of input is the key and based on that the 8th byte, 9th byte,10th byte which comes in multiple lines should come in single record (if key matches)

Code:
Input is

ABCDEF X 
ABCDEF   X
ABCDEF      <b>
MNOPQR X
MNOPQR <b> <b><b>
MNOPQR <b><b><b>
UVWXYZ <b><b><b> 


Code:
Output is

ABCDEF XX<b>
MNOPQR X<b><b>
UVWXYZ<b><b><b>


Whether this is possible using SORT card ?
Please help.

Thanks
Vinu


Vinu,

First it looks like folks are really upset with you. Don't dispair. Just relax for a moment and if you have SAS at your shop then you have the perfect solution [tool] for your problem. Neither SyncSort nor DFSORT is the best answer. Imagine tring to build a dog house out of Tinker Toys. It can be done but it's not the right item to use. SAS is however.

Even with having the perfect tool then - which is simple and very easy to use you still must take a little time to understand it. Sadly this will take at least a day [about 8 hours] to do this particular item properly. But once you do you'll throw rocks at anything else.

I'll explain in English rather than dp for a bit. What you are asking for is a merge. A real merge not what sort does and calls merge which is really an interleave. Interleaving is like shuffling a card deck. You have 52 cards that need to be in a special order without any real changes in the size or shape thereof - unless you get way too complicated [see above about the dog house].

Real merging is precisely what you want and need along with a compression to take out the extra spaces. SAS is specifically designed to take care of just what you want and with a little practice with some of their simple functions - like compressing out extra spaces - you can be up and running in no time. In fact the code is something like this [after you've created a SAS dataset with merging techniques]:

Code:
a='UVWXYZ <b><b><b>';
b=compress(a);
put b;

results would be:

UVWXYZ<b><b><b>


I realize that what you've asked for is a bit more complicated and I hardcoded the variable "a" for illustration.

Once again see if your shop supports SAS. If so then talk to a SAS programmer as what you've asked for should be a snap. Or possibly try some of the free SAS videos on the web. Once again make sure your shop supports SAS before you get that ball rolling.

Trust me.

r
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1715
Location: UK

PostPosted: Sat Jul 13, 2013 3:34 am    Post subject:
Reply with quote

From the example you gve, Dale, I see no reso;ution of TS's requirement - you are just stripping a blank wheras TS wants to dtake the data from 2 to 4 records and produce one records with all the data on it. If that is what you example is supposed to show then - fine. The next question would be: does his shop actually have SAS? In 40 years (less one month) I have never, knowingly, worked in a shop that does have it.
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 -> JCL & VSAM All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm


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