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

Can Syncsort be used to get the below output?


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

New User


Joined: 20 Feb 2007
Posts: 16
Location: New York

PostPosted: Tue Apr 28, 2009 5:43 pm
Reply with quote

Hi All,

My input file is

Code:
Acct num  Product   Amount Currency
1         p1        A1        USD
1         p1        A2        USD
1         p2        A3        INR
1         p3        A4        INR
1         p3        A5        USD
2         p1        A1        USD
2         p1        A2        USD
2         p2        A3        INR
2         p3        A4        INR
2         p3        A5        USD
3         p1        A1        USD
3         p1        A2        USD
3         p2        A3        INR
3         p3        A4        INR
3         p3        A5        USD

My required output should be

Code:
Acct num  Product   Amount Currency
1         p1        A1+A2     USD
1         p2        A3        INR
1         p3        A4        INR
1         p3        A5        USD
2         p1        A1+A2     USD
2         p2        A3        INR
2         p3        A4        INR
2         p3        A5        USD
3         p1        A1+A2     USD
3         p2        A3        INR
3         p3        A4        INR
3         p3        A5        USD


Thanks,
Rahul.
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table

PostPosted: Tue Apr 28, 2009 6:13 pm
Reply with quote

You are kidding, right?
Have you looked at your manual?
SyncSort for z/OS 1.3 Programmer’s Guide wrote:
SUM Control Statement
The SUM control statement deletes records with equal control fields and optionally sums specified numeric fields on those records. Equal keyed records are processed pair by pair. If numeric fields are to be summed, the data in the summed fields are added, the sum is placed in one of the records, and the other record is deleted.
Back to top
View user's profile Send private message
vinothsubramanian

New User


Joined: 01 Sep 2006
Posts: 39
Location: Chennai, India

PostPosted: Tue Apr 28, 2009 6:47 pm
Reply with quote

Code:
Code:
//SORTCPY  EXEC PGM=ICETOOL                                             
//TOOLMSG  DD  SYSOUT=*                                                 
//DFSMSG   DD  SYSOUT=*                                                 
//SYSOUT   DD  SYSOUT=*                                                 
//SYSPRINT DD  SYSOUT=*                                                 
//FILE01   DD  *                                                       
ACCT NUM  PRODUCT   AMOUNT CURRENCY                                     
1         P1        A1        USD                                       
1         P1        A2        USD                                       
1         P2        A3        INR                                       
1         P3        A4        INR                                       
1         P3        A5        USD                                       
2         P1        A1        USD                                       
2         P1        A2        USD                                       
2         P2        A3        INR                                       
2         P3        A4        INR                                       
2         P3        A5        USD                                       
3         P1        A1        USD                                       
3         P1        A2        USD                                       
3         P2        A3        INR                                       
3         P3        A4        INR                                       
3         P3        A5        USD                                       
//TEMP1    DD  DSN=&&TEMP1,SPACE=(CYL,(5,5),RLSE),DISP=(MOD,PASS)       
//FILE02   DD  SYSOUT=*                                                 
//TOOLIN   DD  *                                                       
   COPY FROM(FILE01) TO(TEMP1)  USING(CTL2)                             
   COPY FROM(FILE01)  TO(FILE02) USING(CTL1)                           
   SPLICE FROM(TEMP1) TO(FILE02) ON(1,23,CH) KEEPNODUPS -               
    WITHALL WITH(26,3) USING(CTL3)                                     
/*                                                                     
//CTL1CNTL DD  *                                                       
 SORT FIELDS=COPY,STOPAFT=1                                           
/*                                                                     
//CTL2CNTL DD  *                                                       
 SORT FIELDS=COPY,SKIPREC=1                                           
 INREC FIELDS=(1,20,31,3,21,10)                                       
 OUTFIL IFTHEN=(WHEN=INIT,BUILD=(1,80,81:SEQNUM,1,ZD,                 
                RESTART=(1,23))),                                     
        IFTHEN=(WHEN=(81,1,ZD,EQ,2),OVERLAY=(26:C'+',27:24,2))         
/*                                                                     
//CTL3CNTL DD  *                                                       
 OUTFIL FNAMES=FILE02,                                                 
 BUILD=(1,20,24,5,5X,21,3,80:X)                                       
/*                                                                     

Output:
Code:
ACCT NUM  PRODUCT   AMOUNT CURRENCY   
1         P1        A1+A2     USD     
1         P2        A3        INR     
1         P3        A4        INR     
1         P3        A5        USD     
2         P1        A1+A2     USD     
2         P2        A3        INR     
2         P3        A4        INR     
2         P3        A5        USD     
3         P1        A1+A2     USD     
3         P2        A3        INR     
3         P3        A4        INR     
3         P3        A5        USD     
[/code]
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Tue Apr 28, 2009 9:24 pm
Reply with quote

Hello,

I guess i'm still on Monday. . . icon_confused.gif

Is there some reason you did not use SUM FIELDS to create the output?

I'm not sure why splice and multiple copy statements are needed. Obviously, there is something i misunderstand. . .
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Wed Apr 29, 2009 12:18 am
Reply with quote

I guess that vinothsubramanian posted a smart solution for string processing
the hint was from ... OVERLAY=(26:C'+', ...

where the resulting field is not the sum of the values but the string "A1+A2"
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Wed Apr 29, 2009 1:17 am
Reply with quote

Hi Enrico,

Wonder what the output should be if this was the input:
Code:

Acct num  Product   Amount Currency
1         p1        A1       USD
1         p1        A1A      USD
1         p1        A2       USD
1         p1        A2A      USD
or if there were 20 1/p1 values (not shown icon_smile.gif )?

d
Back to top
View user's profile Send private message
rahulcherekar

New User


Joined: 20 Feb 2007
Posts: 16
Location: New York

PostPosted: Wed Apr 29, 2009 9:32 am
Reply with quote

Hi,

I can use below sort but it is for only one key.

SORT FIELDS=(1,08,CH,A)
OUTFIL REMOVECC,NODETAIL,
SECTIONS=(1,08,
TRAILER3=(1,08,69:TOT=(69,20,SFF,EDIT=(STTTTTTTTTTTTTTT.TTT),
SIGNS=(+,-))))

Here in my case I have 3 keys. Is there anyway to add more keys to SECTIONS? If yes, then what is the syntax?

Thanks,
Rahul.
Back to top
View user's profile Send private message
vinothsubramanian

New User


Joined: 01 Sep 2006
Posts: 39
Location: Chennai, India

PostPosted: Wed Apr 29, 2009 2:56 pm
Reply with quote

Hi Rahul,

You can give the following in case of two fields (assuming your second field starts from 11th Position and length 8):

SORT FIELDS=(1,08,CH,A,11,8,CH,A)

If you have three fields then append the third column at the end.

But still if you want the exact SORT, please provide the starting poistion, length and datatype of the three columns which is to be the key.

Hi Dick

I tried that considering the amount column as string as mentioned by Enricho. But I never thought of the case pointed by you. I never thought that whatever Rahul has requested is for summing up fields. I thought it was just string manipulations based on the key.

Finally, You were correct in understanding Rahul's question intially. I hope that's because of your experience. Soon, I will also learn from you people in understanding the questions.

Great Forum to be sticked to.

Regards,
Ram.
Back to top
View user's profile Send private message
rahulcherekar

New User


Joined: 20 Feb 2007
Posts: 16
Location: New York

PostPosted: Wed Apr 29, 2009 3:08 pm
Reply with quote

Thanks Ram & CICS guy, your suggestions too worked fine. Also the sort card used by me also worked. I used acctnum,ccy and product as the key. I modified the file to have key in the front and then I used following sort card to get the desired output.

SORT FIELDS=(1,4,CH,A,5,9,CH,A,14,3,CH,A,17,9,CH,A)
OUTFIL REMOVECC,NODETAIL,
SECTIONS=(1,27,
TRAILER3=(1,27,28:TOT=(28,17,SFF,EDIT=(STTTTTTTTTTTTT.TT),
SIGNS=(+,-))))

Thanks all. Its a great forum, where one can find any mainframe solution.

Thanks,
Rahul.
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

 


Similar Topics
Topic Forum Replies
No new posts Sortjoin and Search for a String and ... DFSORT/ICETOOL 1
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Joinkeys - 5 output files DFSORT/ICETOOL 7
No new posts Build a record in output file and rep... DFSORT/ICETOOL 11
No new posts XDC SDSF output to temp dataset CLIST & REXX 4
Search our Forums:

Back to Top