View previous topic :: View next topic
Author
Message
surya4ug New User Joined: 08 Jul 2008Posts: 62 Location: chennai
Hello,
I have a file that has records like
header1
---------
A1 1
A2 2
A3 3
header2
---------
A1 10
A2 20
A3 30
header3
---------
A1 100
A2 200
A3 300
The final file needs to be this way:
header 1 header 2 header 3 TOTAL COUNT
A1 1 10 100 111
A2 2 20 200 222
A3 3 30 300 333
Kindly help me find a way... Thanks in advance
Back to top
surya4ug New User Joined: 08 Jul 2008Posts: 62 Location: chennai
Sorry the format wasn't shown properly in the original post
Attribute header 1 header 2 header 3 TOTAL COUNT
A1 1 10 100 111
A2 2 20 200 222
A3 3 30 300 333
Back to top
Skolusu Senior Member Joined: 07 Dec 2007Posts: 2205 Location: San Jose
surya4ug,
Do you only have 3 records per key? or do you have more than that?
what is the position and format of the key and the amount values?
Back to top
surya4ug New User Joined: 08 Jul 2008Posts: 62 Location: chennai
Hello Kolusu,
Thanks for a quick response. Lets consider this example:
Header 1, 2, 3 ............n mean -> file 1,2,3.......file n
A1,A2,A3 are customer attribute differences observed between two databases :
First name diff, Last name diff, SSN diff....etc... there are 85 such attributes. So the input file looks like:
File1
-----
First name diff 1
Last name diff 2
SSN diff 3
File2
-----
First name diff 10
Last name diff 20
SSN diff 30
File3
------
First name diff 100
Last name diff 200
SSN diff 300
Layout
----------
Header (1,15)
----------------
Attribute name ( 1,40 )
Count ( 50,10 )
Final file should look like
FILE1 FILE2 FILE3 TOTAL
FIRST NAME DIFF 1 10 100 111
LAST NAME DIFF 2 20 200 222
SSN DIFF 3 30 300 333
Output file positions can be anything that suits above.
Back to top
surya4ug New User Joined: 08 Jul 2008Posts: 62 Location: chennai
Column1 FILE1 FILE2 FILE3 TOTAL
FIRST NAME DIFF 1 10 100 111
LAST NAME DIFF 2 20 200 222
SSN DIFF 3 30 300 333
Back to top
surya4ug New User Joined: 08 Jul 2008Posts: 62 Location: chennai
Please find the attached layout.
Back to top
Skolusu Senior Member Joined: 07 Dec 2007Posts: 2205 Location: San Jose
surya4ug wrote:
Header 1, 2, 3 ............n mean -> file 1,2,3.......file n First name diff, Last name diff, SSN diff....etc... there are 85 such attributes.[/b]
Your requirements are confusing and you did not even answer all of my questiosn. I asked as to what is the format and length of the attributes involved. The excel layout you attached is absolutely useless.
Also what happens if you find FIRST NAME DIFF in 150 files? Do you want to write all of these 150 records into a single line? What will you do when it exceeds the max allowable length for a sequential dataset?
Back to top
surya4ug New User Joined: 08 Jul 2008Posts: 62 Location: chennai
Hello,
My apologies. I thought i mentioned the positions clearly, but on seeing the post i understand that it was pretty vague.
Attached is the output & input file layout with start and end position of each attribute.
please let me know if i need to clarify anything else.
Back to top
Skolusu Senior Member Joined: 07 Dec 2007Posts: 2205 Location: San Jose
The following DFSORT JCL will give you the desired results. Concatenate all your input files together and I assumed that every file has a header saying "ATTRIBUTE" which will use an indicator for grouping the records.
Code:
//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=Your input file 01,DISP=SHR
// DD DSN=Your input file 02,DISP=SHR
// DD DSN=Your input file 03,DISP=SHR
// DD DSN=Your input file 04,DISP=SHR
// DD DSN=Your input file 05,DISP=SHR
// DD DSN=Your input file 06,DISP=SHR
// DD DSN=Your input file 07,DISP=SHR
// DD DSN=Your input file 08,DISP=SHR
// DD DSN=Your input file 09,DISP=SHR
// DD DSN=Your input file 10,DISP=SHR
//SORTOUT DD SYSOUT=*
//SYSIN DD *
INREC IFTHEN=(WHEN=INIT,BUILD=(1,50,100C'0',70,10,SFF,ZD,LENGTH=10)),
IFTHEN=(WHEN=GROUP,BEGIN=(1,9,CH,EQ,C'ATTRIBUTE'),PUSH=(161:ID=2)),
IFTHEN=(WHEN=(161,2,ZD,EQ,01),OVERLAY=(051:151,10)),
IFTHEN=(WHEN=(161,2,ZD,EQ,02),OVERLAY=(061:151,10)),
IFTHEN=(WHEN=(161,2,ZD,EQ,03),OVERLAY=(071:151,10)),
IFTHEN=(WHEN=(161,2,ZD,EQ,04),OVERLAY=(081:151,10)),
IFTHEN=(WHEN=(161,2,ZD,EQ,05),OVERLAY=(091:151,10)),
IFTHEN=(WHEN=(161,2,ZD,EQ,06),OVERLAY=(101:151,10)),
IFTHEN=(WHEN=(161,2,ZD,EQ,07),OVERLAY=(111:151,10)),
IFTHEN=(WHEN=(161,2,ZD,EQ,08),OVERLAY=(121:151,10)),
IFTHEN=(WHEN=(161,2,ZD,EQ,09),OVERLAY=(131:151,10)),
IFTHEN=(WHEN=(161,2,ZD,EQ,10),OVERLAY=(141:151,10))
SORT FIELDS=(1,50,CH,A),EQUALS
SUM FIELDS=(051,10,061,10,071,10,081,10,091,10,
101,10,111,10,121,10,131,10,141,10),FORMAT=ZD
OUTFIL OMIT=(1,9,CH,EQ,C'ATTRIBUTE'),REMOVECC,
BUILD=(1,50,
070:051,10,ZD,M10,LENGTH=10,082:061,10,ZD,M10,LENGTH=10,
093:071,10,ZD,M10,LENGTH=10,104:081,10,ZD,M10,LENGTH=10,
115:091,10,ZD,M10,LENGTH=10,126:101,10,ZD,M10,LENGTH=10,
137:111,10,ZD,M10,LENGTH=10,148:121,10,ZD,M10,LENGTH=10,
159:131,10,ZD,M10,LENGTH=10,170:141,10,ZD,M10,LENGTH=10,
181:051,10,ZD,ADD,061,10,ZD,ADD,071,10,ZD,ADD,
081,10,ZD,ADD,091,10,ZD,ADD,101,10,ZD,ADD,
111,10,ZD,ADD,121,10,ZD,ADD,131,10,ZD,ADD,
141,10,ZD,M10,LENGTH=12),
HEADER1=(070:'MISMATCHES FROM 10 FILES',/,
001:'ATTRIBUTE ',070:'FILE01 VAL',082:'FILE02 VAL',
093:'FILE03 VAL',104:'FILE04 VAL',115:'FILE05 VAL',
126:'FILE06 VAL',137:'FILE07 VAL',148:'FILE08 VAL',
159:'FILE09 VAL',170:'FILE10 VAL',181:7X,'TOTAL')
/*
Back to top
surya4ug New User Joined: 08 Jul 2008Posts: 62 Location: chennai
Thank u!!
I have a doubt here..
IFTHEN=(WHEN=GROUP,BEGIN=(1,9,CH,EQ,C'ATTRIBUTE'),PUSH=(161:ID=2)),
Here, what is the PUSH attribute doing and where is the ID picked from? Does it refer to the file no? 01,02...?
Back to top
Frank Yaeger DFSORT Developer Joined: 15 Feb 2005Posts: 7129 Location: San Jose, CA
ID=2 "pushes" a 2-character ID into the records of each group starting with '01' for the first group, '02' for the second group, etc.
For complete details on DFSORT's WHEN=GROUP function, see:
Use [URL] BBCode for External Links
Back to top
surya4ug New User Joined: 08 Jul 2008Posts: 62 Location: chennai
My bad!! I should have checked it... thanks again!
Finally, 1 error is appearing again and again:
INREC STATEMENT : SYNTAX ERROR
in the spool, i see an asterisk beneath WHEN=GROUP
INREC IFTHEN=(WHEN=INIT,BUILD=(1,50,100C'0',61,10,SFF,ZD,LENGTH=10)),
IFTHEN=(WHEN=GROUP,BEGIN=(1,9,CH,EQ,C'GPSI-CARI'),PUSH=(161:ID=2)),
I dont see anything weird in the JCL...
Back to top
dick scherrer Moderator Emeritus Joined: 23 Nov 2006Posts: 19243 Location: Inside the Matrix
Hello,
Is there some reason you did not post the message id?
Back to top
surya4ug New User Joined: 08 Jul 2008Posts: 62 Location: chennai
Hello,
This is the message:
WER268A INREC STATEMENT : SYNTAX ERROR
WER211B SYNCSMF CALLED BY SYNCSORT; RC=0000
WER449I SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE
I guess syncsort version used doesn't support WHEN=GROUP ( i saw a post some where in expert forum )..
Please let me know if the same output can be obtained in a different way.
Back to top
Frank Yaeger DFSORT Developer Joined: 15 Feb 2005Posts: 7129 Location: San Jose, CA
This is the DFSORT Forum for DFSORT questions. Why does your Subject say using DFSORT if you're not using DFSORT? Syncsort questions should be posted to JCL Forum.
Kolusu and I are DFSORT developers. We're happy to answer questions on DFSORT and DFSORT's ICETOOL, but we don't answer questions on Syncsort.
I'm moving this thread to JCL Forum and changing the Subject.
Back to top
surya4ug New User Joined: 08 Jul 2008Posts: 62 Location: chennai
I was inclined to use DFSORT initially, but came to know just some time back that WHEN=GROUP was failing.
Please let me know how the requirement can be fulfilled using SYNCSORT..
Back to top
dick scherrer Moderator Emeritus Joined: 23 Nov 2006Posts: 19243 Location: Inside the Matrix
Back to top
surya4ug New User Joined: 08 Jul 2008Posts: 62 Location: chennai
Hello,
Thanks for your prompt response. I hate to say this, but i came to know that we can't get the recent version of syncsort in near future .
Is there any other way.
Back to top
dick scherrer Moderator Emeritus Joined: 23 Nov 2006Posts: 19243 Location: Inside the Matrix
Hello,
Quote:
Is there any other way.
You could always do what we did before the sort products were "programmable". . .
Simply write the code.
Back to top
Arun Raj Moderator Joined: 17 Oct 2006Posts: 2481 Location: @my desk
surya ,
Which version of SyncSort are you using? You can see this info on the top of your SYSOUT messages.
Do you have the header - "attribute ( 1-50 ) count ( 70- 80 ) " in each file file as your excel attachment indicates?
File1
Code:
attribute ( 1-50 ) count ( 70- 80 )
FIELD 1 CHANGES 20071
FIELD 2 CHANGES 92
............
File2
Code:
attribute ( 1-50 ) count ( 70- 80 )
FIELD 1 CHANGES 20033
FIELD 2 CHANGES 111
............
Back to top
surya4ug New User Joined: 08 Jul 2008Posts: 62 Location: chennai
Hello,
'Attribute' exists physically in the input file ( 1-50 ). 'Count' doesn't.
Back to top
surya4ug New User Joined: 08 Jul 2008Posts: 62 Location: chennai
I'm using the following version of syncsort:
SYNCSORT FOR Z/OS 1.3.0.2R
Back to top
surya4ug New User Joined: 08 Jul 2008Posts: 62 Location: chennai
Hello,
Sorry for more trouble. But i have another requirement now. Using SYNCSORT, can i divide the TOTAL field obtained in the output file by a constant num and multiply by 100 to get the % and write into the very same output file.
I didnot mean to throw in all reqmts at a time, we can go one after the other. But just wanted to let u know my reqmt
Thanks in advance!
Back to top
Arun Raj Moderator Joined: 17 Oct 2006Posts: 2481 Location: @my desk
surya4ug,
Replied via PM.
Back to top
surya4ug New User Joined: 08 Jul 2008Posts: 62 Location: chennai
Thank u ..i replied too
Back to top
Please enable JavaScript!