|
View previous topic :: View next topic
|
| Author |
Message |
Vijayakumar Mani
New User
Joined: 24 Oct 2007
Posts: 11
Location: Salem
|
|
|
|
Is there any way to get the common values present in multiple files(more than 2 files) - using Syncsort? The values that are present in all files has to be written to another file.
Example:
File1:
File2:
File3:
Expected Output:
|
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
One way would be to use JOINKEYS and use 2 steps. One to join files 1 & 2, the other to join the matched output from that step to file 3. |
|
| Back to top |
|
|
Vijayakumar Mani
New User
Joined: 24 Oct 2007
Posts: 11
Location: Salem
|
|
|
|
Hi dick scherrer,
Thank you for your information. I have tried the JOINKEYS and it works fine. But the number of steps increases as files are in more numbers.
Is there any other way(using Syncsort or other than Syncsort) to get the same result in a single step? |
|
| Back to top |
|
|
Itanium
Active User
Joined: 22 Jan 2006
Posts: 114
Location: India
|
|
|
|
you can try the code, SORTXSUM File will have the required values.
| Code: |
//R010 EXEC PGM=SORT
//SORTIN DD DSN=FILE1,DISP=SHR
// DD DSN=FILE2,DISP=SHR
// DD DSN=FILE3,DISP=SHR
//SORTOUT DD DSN=FILEOUT,
// DISP=(NEW,CATLG,CATLG),OTHER PARAMETERS
//SORTXSUM DD DSN=FILEXSUM,
// DISP=(NEW,CATLG,CATLG),OTHER PARAMETERS
//SYSIN DD *
SORT FIELDS=(1,1,CH,A),XSUM
SUM FIELDS =NONE
/*
//SYSOUT DD SYSOUT=* |
|
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2482
Location: @my desk
|
|
|
|
Vijayakumar,
Do you have duplicate key values in the same file???
Thanks,
Arun |
|
| Back to top |
|
|
sril.krishy
Active User
Joined: 30 Jul 2005
Posts: 183
Location: hyderabad
|
|
|
|
| Quote: |
But the number of steps increases as files are in more numbers.
|
Vijayakumar Mani,
You can use the SYNCSORT with a ONE STEP for multiple files to achive the desired output.Please provide dummy input and expected output so that you can get quick and correct solution.
Thanks
Krishy |
|
| Back to top |
|
|
Vijayakumar Mani
New User
Joined: 24 Oct 2007
Posts: 11
Location: Salem
|
|
|
|
Hi Itanium,
I have tried the code given by you with small change as below.
SORT FIELDS=(1,1,CH,A)
SUM FIELDS=NONE,XSUM
But the output is not as expected. I am getting the below output (Input is as specified in the beginning of my post with 3 files).
| Quote: |
| Do you have duplicate key values in the same file??? |
Hi Arun,
There will not be duplicate values in the same file.
| Quote: |
| Please provide dummy input and expected output |
Hi Krishy,
I have provided the dummy input and expected output in the beginning of my post. |
|
| Back to top |
|
|
Vijayakumar Mani
New User
Joined: 24 Oct 2007
Posts: 11
Location: Salem
|
|
|
|
Hi Itanium,
The XSUM parameter should be used if you want records deleted by SUM processing to be written to a data set defined by the SORTXSUM DD statement.
Since you have concatenated the 3 files in SORTIN DD statement and specified SUM FIELDS=NONE, the duplicate values will be deleted and those deleted values will be stored in datset defined by the SORTXSUM DD statement.
My requirement is different. I want the particular values found in all files to be written to an output dataset. I think i have specified my requirement clearly in the first post itself.
Please let me know if there is any concern.
Thanks,
Vijay |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2482
Location: @my desk
|
|
|
|
Hi Vijyay,
On a first thought can you try using these.............
First step - XSUM step as in your previous post. The output from this goes as next step's input.
Second step card
| Quote: |
INREC FIELDS=(1,1,2:C'0001')
SORT FIELDS=(1,1,CH,A)
SUM FIELDS=(2,4,ZD)
OUTFIL FNAMES=SORTOUT,INCLUDE=(2,4,ZD,EQ,2),
OUTREC=(1,1)
|
The bolded value = actual number of input files - 1.
Thanks,
Arun |
|
| Back to top |
|
|
sril.krishy
Active User
Joined: 30 Jul 2005
Posts: 183
Location: hyderabad
|
|
|
|
Vijayakumar Mani,
check the below code.
| Code: |
//PS020 EXEC PGM=SYNCSORT
//SYSOUT DD SYSOUT=*
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD YOUR I/P FILE 1
DD YOUR I/P FILE 2
//T0 DD DSN=&T0,DISP=(,PASS),SPACE=(TRK,(1,1),RLSE)
//T1 DD DSN=&T1,DISP=(,PASS),SPACE=(TRK,(1,1),RLSE)
//CON DD YOUR INPUT FILE 3
// DD DSN=&T0,DISP=(OLD,PASS),VOL=REF=*.T0
//OUT DD SYSOUT=*
//TOOLIN DD *
SELECT FROM(IN1) TO(T0) ON(1,1,CH) ALLDUPS
SELECT FROM(CON) TO(T1) ON(1,1,CH) ALLDUPS
SORT FROM(T1) TO(OUT) USING(CTL1)
//CTL1CNTL DD *
SORT FIELDS=(1,1,CH,A),EQUALS
SUM FIELDS=NONE
|
OUT is going to contain the final o/p.
Thanks
Krishy |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
I may be mistaken, but i believe this has gone in a different direction than the original request.
If i understood, the output should be only the values that appear in every file. If there were 3 files, only values appearing in all 3 files would go to output. If there were 20 files, onle values appearing in all 20 files would appear in the output.
So far, i believe nothing posted except JOINKEYS will do this. . .
I'm sure we'll let me know if i'm mistaken  |
|
| Back to top |
|
|
gcicchet
Senior Member
Joined: 28 Jul 2006
Posts: 1702
Location: Australia
|
|
|
|
Hi,
assuming no dups in any file, you can try this
| Code: |
//S1 EXEC PGM=ICEMAN
//SYSOUT DD SYSOUT=*
//SORTIN DD * FILE1
1
2
3
/*
// DD * FILE2
2
5
6
3
7
/*
// DD * FILE3
4
2
1
3
/*
//SORTOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=(1,1,CH,A)
OUTREC OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,1))
OUTFIL INCLUDE=(81,8,ZD,EQ,+3),BUILD=(1,80)
/*
|
Gerry |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2482
Location: @my desk
|
|
|
|
Hello Dick,
| Quote: |
| So far, i believe nothing posted except JOINKEYS will do this. . . |
I made the posting after testing with sample data. But it needed two steps.
I have an alternative with a single card as below.
| Code: |
INREC OVERLAY=(81:C'0001')
SORT FIELDS=(1,1,CH,A)
SUM FIELDS=(81,4,ZD)
OUTFIL FNAMES=SORTOUT,INCLUDE=(81,4,ZD,EQ,'number of input files'),
OUTREC=(1,80)
|
I believe Gerry's card also needs to be modified with the number of input files.
Thanks,
Arun |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hi Arun,
| Quote: |
| I believe Gerry's card also needs to be modified with the number of input files. |
Yup, it will
If the number of files to process is dynamic, a "front-end" process might detect the number of files and generate the "number of input fles" in whatever code. . . |
|
| Back to top |
|
|
Vijayakumar Mani
New User
Joined: 24 Oct 2007
Posts: 11
Location: Salem
|
|
|
|
| Quote: |
| I have an alternative with a single card as below |
Hi Arun,
Thanks a lot. Its is working fine and that is what i needed.
Thanks,
Vijay |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
