|
View previous topic :: View next topic
|
| Author |
Message |
vinu78
Active User
Joined: 02 Oct 2008
Posts: 179
Location: India
|
|
|
|
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 |
|
 |
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
How many keys do you have which could match, just three as you show, or more?
RECFM/LRECL of input file. |
|
| Back to top |
|
|
vinu78
Active User
Joined: 02 Oct 2008
Posts: 179
Location: India
|
|
|
|
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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Are you saying that four is the maximum number of records which could have the same key?
Anything else I asked? |
|
| Back to top |
|
|
expat
Global Moderator
Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
|
|
|
|
| You say 3 or 4 records ........ is there no merge for only two matches ??? |
|
| Back to top |
|
|
vinu78
Active User
Joined: 02 Oct 2008
Posts: 179
Location: India
|
|
|
|
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 |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA
|
|
|
|
| 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 |
|
|
expat
Global Moderator
Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
|
|
|
|
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 |
|
|
vinu78
Active User
Joined: 02 Oct 2008
Posts: 179
Location: India
|
|
|
|
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 |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| 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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
vinu78
Active User
Joined: 02 Oct 2008
Posts: 179
Location: India
|
|
|
|
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 |
|
|
vinu78
Active User
Joined: 02 Oct 2008
Posts: 179
Location: India
|
|
|
|
Hi All,
Whether OVERLAY method used in SORT card will solve this.
Thanks
Vinu |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| Is there a question there? Assuming there is - what happened when you tried it? |
|
| Back to top |
|
|
vinu78
Active User
Joined: 02 Oct 2008
Posts: 179
Location: India
|
|
|
|
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 |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
You need to post the JCL and control statements you used and the informational/diagnostic information generated by the run. |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
| 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 |
|
|
Dale Robertson
New User
Joined: 21 Jun 2013
Posts: 44
Location: U.S.A.
|
|
|
|
| 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 |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| 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 |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
| Before jumping into conclusions, should n't we be clear on what the TS actually wants and let him respond to the queries raised and which are still unanswered. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hi Arun,
| Quote: |
| should n't we be clear on what the TS actually wants |
Yup. . .
Until/unless TS provides better info we're just flailing away.
Maybe Vinu will be back on Monday . . .
d |
|
| Back to top |
|
|
vinu78
Active User
Joined: 02 Oct 2008
Posts: 179
Location: India
|
|
|
|
| Sorry everybody. Actually I was out of country. Also my confidence dipped down and I am afraid to post further as I have messed up my wordings. Once again I am sorry to everybody |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Don't worry about "messed wording". It may take a few iterations, but we Can work thru it.
What is your status for this now? |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
Vinu,
We just want you to pay attention to the details requested for, and just provide them. None here is 'upset' with your 'wordings'.
But when someone takes the time to read through your posts and asks for details just to help you, it does n't seem right to ignore it.
Please feel free to post with confidence, Have a nice day  |
|
| Back to top |
|
|
|
|
