Join 2 files using sync sort

Tharageshwari Marukkutti · New User Joined: 10 Oct 2007 Posts: 12 Location: India

I need to merge 2 files based on the first column in File1 and File 2. Thefirst 2 columns are of variable length. first column in the file2 should match with the prefix of first column in the File1.
How could I get this using SYnc sort ... Please assist

File1

Bill Woodger · Posted: Tue Feb 26, 2013 4:22 pm

If you have SyncSort, please post your questions in the JCL part of the forum.

Anuj Dhawan · Posted: Tue Feb 26, 2013 5:30 pm

I'm not sure if I understood it very well - what do you call as "prefix of first column in the File1"? Also, what is it telling - "Thefirst 2 columns are of variable length"? Did you mean, input files are VB?

With your example, it looks like -- you want to compare two files and keep the duplicates as well. Perhaps, JOINKEYs example will you give some clue until you explain better.

Bill Woodger · Posted: Tue Feb 26, 2013 5:41 pm

I'm "guessing", from the rest of the data, that the "first field" is a DSN. So the first element of that field is the HLQ, of variable-length, ending with ".".

JOINKEYS, yes. But with your SyncSort, do you have the JNFnCNTL files available? If so, PARSE the HLQ to a temporary extension to the record, use that in the JOINKEYS.

If not, you'll need another step to make the extension "permanent" (across the two steps, can be a temporary dataset) and then do the JOINKEYS in the second step.

Tharageshwari Marukkutti · New User Joined: 10 Oct 2007 Posts: 12 Location: India

"prefix of first column in the File1" Means first column in File1 matches with the starting few characters of first column in File1

yes File1 and File2 are of variable lenght.

Bill Woodger · Posted: Tue Feb 26, 2013 5:56 pm

You mean my guess was bad, and what you want is some type of "wildcard" search?

Is your sample data representative of the format of the data you are processing, ie is it all one long bunch of characters, with nothing to "delimit" it?

Tharageshwari Marukkutti · New User Joined: 10 Oct 2007 Posts: 12 Location: India

File1 first column is the dataset name (44 characters ie tsotmee.test.file.batch) and File2 first column is the dataset pattern ( i.e tsotmee.test)

Nic Clouston · Posted: Wed Feb 27, 2013 7:02 am

Anuj Dhawan · Posted: Wed Feb 27, 2013 12:15 pm

At first, it looked like if you wnat to match two files on some keys and wants to keep the duplicates, but now, I don't have any clue of what you're trying to accomplish.

Tharageshwari Marukkutti · New User Joined: 10 Oct 2007 Posts: 12 Location: India

I'm looking for SORT jcl. I believe my example clearly tell what i really need.

expat · Posted: Wed Feb 27, 2013 2:46 pm

Tharageshwari Marukkutti

If your example clearly tells us what you need, then why are there so many questions being asked.

You need to give as much detail as possible, including in the sample data supplied. If the data is meant to represent a DSN or a part DSN, then show your data like that.

The more detail that YOU give cuts down on the ambiguity of peoples understanding of your questions, and also any lost time trying to help you because they have interpretted the requirement incorrectly.

Bill Woodger · Posted: Wed Feb 27, 2013 3:50 pm

Tharageshwari Marukkutti,

Even the title you gave causes confusion. "Merge", in terms of Sort products, means something which bears no relation to the very little information that you have provided.

Show some good sample data, and expected output, for what you want.

Have you tried anything, had any thoughts about how to approach it?

You feel you have been clear. You have not. Be clear, and be aware that if you "change your mind" later (ie don't describe what you want to the fullest degree), you are even less likely to get a solution.

Nic Clouston · Posted: Fri Mar 01, 2013 2:19 pm

Bill Woodger · Posted: Fri Mar 01, 2013 4:01 pm

Well, after sowing nothing but confusion, Tharageshwari Marukkutti seems to have left the building. Waste of a topic.

dick scherrer · Posted: Sun Mar 03, 2013 9:02 pm

Hello,

Bill Woodger · Posted: Sun Mar 03, 2013 9:43 pm

Well, since I've gone to the bother of thinking of a solution, it would be nice :-)

Tharageshwari Marukkutti · New User Joined: 10 Oct 2007 Posts: 12 Location: India

Sorry the topic i had put in as "Merge" was wrong. Actually I need to join 2 files

Here is what i have to do

FILE1 is of length 80, first field starts at position 1 and of length 44, second field starts at 45 and length of 2

FILE1

Bill Woodger · Posted: Wed Mar 06, 2013 6:14 pm

OK, once the sample data was shown, it was clear that earlier thoughts were a waste of time.

There is a JOINKEYS solution, but it would not that easily keep the order of the output file without another Sort.

So, here's a step to generate Sort Control cards for the following step. Since any blank lines in the input would produce invalid control cards, I treat them as "comments" (blank in column one) and so decided to support "comments" as well, "*" in column one. Examples of what are treated as comments are included in the data.

SORTOUT will be fed into the next step.

It might seem long, due to the use of symbols/SYMNAMES and attempting to further make the code easier to follow, so that it can be adapted for other situations without requiring decryption skills. The Control Cards after symbol-substitution are included, it can be seen to be not really all that long.

It works by counting the length of the field starting in position one on the "data to be updated from" file, using a version of HUMPHREY for fixed-length records. If there were variable-length records, base the counting on the original HUMPHREY.