IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

File formatting using SORT


IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
surya4ug

New User


Joined: 08 Jul 2008
Posts: 62
Location: chennai

PostPosted: Thu May 21, 2009 10:34 pm
Reply with quote

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
View user's profile Send private message
surya4ug

New User


Joined: 08 Jul 2008
Posts: 62
Location: chennai

PostPosted: Thu May 21, 2009 10:37 pm
Reply with quote

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
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Thu May 21, 2009 10:45 pm
Reply with quote

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
View user's profile Send private message
surya4ug

New User


Joined: 08 Jul 2008
Posts: 62
Location: chennai

PostPosted: Fri May 22, 2009 12:24 am
Reply with quote

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
View user's profile Send private message
surya4ug

New User


Joined: 08 Jul 2008
Posts: 62
Location: chennai

PostPosted: Fri May 22, 2009 12:26 am
Reply with quote

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
View user's profile Send private message
surya4ug

New User


Joined: 08 Jul 2008
Posts: 62
Location: chennai

PostPosted: Fri May 22, 2009 12:27 am
Reply with quote

Please find the attached layout.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Fri May 22, 2009 2:53 am
Reply with quote

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? icon_question.gif What will you do when it exceeds the max allowable length for a sequential dataset?
Back to top
View user's profile Send private message
surya4ug

New User


Joined: 08 Jul 2008
Posts: 62
Location: chennai

PostPosted: Fri May 22, 2009 8:54 pm
Reply with quote

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
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Fri May 22, 2009 11:07 pm
Reply with quote

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
View user's profile Send private message
surya4ug

New User


Joined: 08 Jul 2008
Posts: 62
Location: chennai

PostPosted: Sat May 23, 2009 3:06 am
Reply with quote

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
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Sat May 23, 2009 3:17 am
Reply with quote

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
View user's profile Send private message
surya4ug

New User


Joined: 08 Jul 2008
Posts: 62
Location: chennai

PostPosted: Sat May 23, 2009 3:25 am
Reply with quote

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
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Sat May 23, 2009 4:23 am
Reply with quote

Hello,

Is there some reason you did not post the message id?
Back to top
View user's profile Send private message
surya4ug

New User


Joined: 08 Jul 2008
Posts: 62
Location: chennai

PostPosted: Sat May 23, 2009 4:39 am
Reply with quote

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
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Sat May 23, 2009 6:13 am
Reply with quote

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
View user's profile Send private message
surya4ug

New User


Joined: 08 Jul 2008
Posts: 62
Location: chennai

PostPosted: Sat May 23, 2009 6:25 am
Reply with quote

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
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Sat May 23, 2009 7:03 am
Reply with quote

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
Back to top
View user's profile Send private message
surya4ug

New User


Joined: 08 Jul 2008
Posts: 62
Location: chennai

PostPosted: Sat May 23, 2009 7:12 am
Reply with quote

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 icon_sad.gif .

Is there any other way.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Sat May 23, 2009 7:53 am
Reply with quote

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
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Sat May 23, 2009 9:12 am
Reply with quote

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
View user's profile Send private message
surya4ug

New User


Joined: 08 Jul 2008
Posts: 62
Location: chennai

PostPosted: Sat May 23, 2009 9:44 am
Reply with quote

Hello,

'Attribute' exists physically in the input file ( 1-50 ). 'Count' doesn't.
Back to top
View user's profile Send private message
surya4ug

New User


Joined: 08 Jul 2008
Posts: 62
Location: chennai

PostPosted: Sat May 23, 2009 9:45 am
Reply with quote

I'm using the following version of syncsort:

SYNCSORT FOR Z/OS 1.3.0.2R
Back to top
View user's profile Send private message
surya4ug

New User


Joined: 08 Jul 2008
Posts: 62
Location: chennai

PostPosted: Sat May 23, 2009 10:40 am
Reply with quote

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 icon_smile.gif

Thanks in advance!
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Sat May 23, 2009 11:12 am
Reply with quote

surya4ug,

Replied via PM.
Back to top
View user's profile Send private message
surya4ug

New User


Joined: 08 Jul 2008
Posts: 62
Location: chennai

PostPosted: Sat May 23, 2009 11:33 am
Reply with quote

Thank u ..i replied too icon_smile.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> JCL & VSAM Goto page 1, 2  Next

 


Similar Topics
Topic Forum Replies
No new posts Unable to interpret a hex value to De... COBOL Programming 4
No new posts JCL sort to compare dates in two file... DFSORT/ICETOOL 2
No new posts Is this possible via sort (in one pass)? SYNCSORT 4
No new posts GDG generation name to GDG Base name ... DFSORT/ICETOOL 3
No new posts SORT on detail record, then repeat he... DFSORT/ICETOOL 3
Search our Forums:

Back to Top