File formatting using SORT

surya4ug · New User Joined: 08 Jul 2008 Posts: 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

surya4ug · New User Joined: 08 Jul 2008 Posts: 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

Skolusu · Posted: Thu May 21, 2009 10:45 pm

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?

surya4ug · New User Joined: 08 Jul 2008 Posts: 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.

surya4ug · New User Joined: 08 Jul 2008 Posts: 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

surya4ug · New User Joined: 08 Jul 2008 Posts: 62 Location: chennai

Please find the attached layout.

Skolusu · Posted: Fri May 22, 2009 2:53 am

surya4ug · New User Joined: 08 Jul 2008 Posts: 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.

Skolusu · Posted: Fri May 22, 2009 11:07 pm

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.

surya4ug · New User Joined: 08 Jul 2008 Posts: 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...?

Frank Yaeger · Posted: Sat May 23, 2009 3:17 am

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

surya4ug · New User Joined: 08 Jul 2008 Posts: 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...

dick scherrer · Posted: Sat May 23, 2009 4:23 am

Hello,

Is there some reason you did not post the message id?

surya4ug · New User Joined: 08 Jul 2008 Posts: 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.

Frank Yaeger · Posted: Sat May 23, 2009 6:13 am

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.

surya4ug · New User Joined: 08 Jul 2008 Posts: 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..

dick scherrer · Posted: Sat May 23, 2009 7:03 am

Hello,

You can do what you need with Syncsort. You need to have the current version on your system.

Refer to this similar topic:
ibmmainframes.com/viewtopic.php?t=40078

surya4ug · New User Joined: 08 Jul 2008 Posts: 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.

dick scherrer · Posted: Sat May 23, 2009 7:53 am

Hello,

Arun Raj · Posted: Sat May 23, 2009 9:12 am

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

surya4ug · New User Joined: 08 Jul 2008 Posts: 62 Location: chennai

Hello,

'Attribute' exists physically in the input file ( 1-50 ). 'Count' doesn't.

surya4ug · New User Joined: 08 Jul 2008 Posts: 62 Location: chennai

I'm using the following version of syncsort:

SYNCSORT FOR Z/OS 1.3.0.2R

surya4ug · New User Joined: 08 Jul 2008 Posts: 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!

Arun Raj · Posted: Sat May 23, 2009 11:12 am

surya4ug,

Replied via PM.

surya4ug · New User Joined: 08 Jul 2008 Posts: 62 Location: chennai

Thank u ..i replied too