Cross join of 2 files

Aneesh · New User Joined: 30 Sep 2006 Posts: 60

Hi,

I have 2 files FB of length 5 bytes each. I want to create an output file that has the cross join of all records from the first file to the second.

Input file 1
11111
22222
33333

Input file 2
AAAAA
BBBBB

Output file
11111AAAAA
11111BBBBB
22222AAAAA
22222BBBBB
33333AAAAA
33333BBBBB

Can this be done using sort?

Thanks in advance,
Aneesh.

dick scherrer · Posted: Thu Jul 02, 2009 4:51 am

Hello,

No.

What you want to do is a cartesian join.

The last several times this was asked (searching this dfsort part of the forum will find these topics), the answer from IBM was that

Skolusu · Posted: Thu Jul 02, 2009 5:27 am

Aneesh,

The following JCL will give you the desired results. I assumed that your input 2 has just 2 records and you are interested in only the first 5 bytes. If you have more records in your file2 , the output will be huge.

Robert Sample · Posted: Thu Jul 02, 2009 5:38 am

SAS PROC SQL without a WHERE clause will do a Cartesian join (every record in file 1 will be matched to every record in file 2, yielding output of the number of records in file 1 times the number of records in file 2).

Googling SAS Cartesian join will get over 14,000 hits including some good explanations of the process.

Skolusu · Posted: Tue Nov 17, 2009 5:03 am

Aneesh,

With z/OS DFSORT V1R5 PTF UK51706 or z/OS DFSORT V1R10 PTF UK51707, you can use the Joinkeys function like shown below to get the desired results. Since both files don't have a common key we add an extra byte at pos 6 using INREC for each file via JNF1CNTL and JNF2CNTL and use that as a key for matching