View previous topic :: View next topic
|
Author |
Message |
Ambili S
Active User
Joined: 06 Sep 2007 Posts: 112 Location: India
|
|
|
|
Hi
I have an input file with Customer and Amount details as follows:
C1 100
C1 100
C1 500
C1 550
C1 500
C2 250
C2 550
C2 900
C3 800
C4 900
C5 100
C5 150
Now i want the last entry of each customer. So the output should be
C1 500
C2 900
C3 800
C4 900
C5 150
I tried using WHEN=GROUP with INREC and want to put a character say 'Y' when the group ends. Then using INCLUDE i can opt for records with Y.
The input should now look like :
C1 100
C1 100
C1 500
C1 550
C1 500 Y
C2 250
C2 550
C2 900 Y
C3 800 Y
C4 900 Y
C5 100
C5 150 Y
Could you please let me know if Y can be overlaid for last entries using icetool ? |
|
Back to top |
|
|
Ambili S
Active User
Joined: 06 Sep 2007 Posts: 112 Location: India
|
|
|
|
The OUTPUT can be achived in other ways also , but i wanted to know if there's any way to overlay the last record of each customer with any char or identifier for e.g. say Y here. |
|
Back to top |
|
|
Escapa
Senior Member
Joined: 16 Feb 2007 Posts: 1399 Location: IL, USA
|
|
|
|
Ambali, Below job will give you expected output(assuming file is sorted on customer).
Code: |
//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
C1 100
C1 100
C1 500
C1 550
C1 500
C2 250
C2 550
C2 900
C3 800
C4 900
C5 100
C5 150
//SORTOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=COPY
OUTFIL REMOVECC,NODETAIL,
SECTIONS=(1,2,TRAILER3=(1,80))
//* |
Output will be
Code: |
C1 500
C2 900
C3 800
C4 900
C5 150 |
|
|
Back to top |
|
|
hailashwin
New User
Joined: 16 Oct 2008 Posts: 74 Location: Boston
|
|
|
|
An alternate way...
Code: |
//SYSIN DD *
SORT FIELDS=(1,2,CH,A,90,8,ZD,D)
INREC OVERLAY=(90:SEQNUM,8,ZD,RESTART=(1,2))
OUTREC OVERLAY=(100:SEQNUM,8,ZD,RESTART=(1,2))
OUTFIL FNAMES=SORTOUT,INCLUDE=(100,8,ZD,EQ,1),BUILD=(1,80)
//*
|
Thanks,
Ashwin. |
|
Back to top |
|
|
giraffe
New User
Joined: 09 Nov 2010 Posts: 27 Location: SHENZHEN CHINA
|
|
|
|
What about use of splice? |
|
Back to top |
|
|
giraffe
New User
Joined: 09 Nov 2010 Posts: 27 Location: SHENZHEN CHINA
|
|
|
|
Hi, I think the solution from Escapa will show better performance because splice will do sorting work with option equals first and then match and overlay. |
|
Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
|
|
|
|
It can also be done with SELECT using a COPY instead of a SORT like this:
Code: |
//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN DD *
C1 100
C1 100
C1 500
C1 550
C1 500
C2 250
C2 550
C2 900
C3 800
C4 900
C5 100
C5 150
/*
//OUT DD SYSOUT=*
//TOOLIN DD *
SELECT FROM(IN) TO(OUT) ON(1,2,CH) LAST USING(CTL1)
/*
//CTL1CNTL DD *
OPTION COPY
/*
|
|
|
Back to top |
|
|
Ambili S
Active User
Joined: 06 Sep 2007 Posts: 112 Location: India
|
|
|
|
Hi Frank
I wanted to know if there is any way to opt for selective records by pushing a char say 'Y' . I was able to achieve the output but , in SORT is there any way we can select records by overlaying a id or character (something like marking the records i want ) . |
|
Back to top |
|
|
Escapa
Senior Member
Joined: 16 Feb 2007 Posts: 1399 Location: IL, USA
|
|
|
|
Ambili S wrote: |
Hi Frank
I wanted to know if there is any way to opt for selective records by pushing a char say 'Y' . I was able to achieve the output but , in SORT is there any way we can select records by overlaying a id or character (something like marking the records i want ) . |
Ambili, What you are trying to explain is really confusing. Can you please show some sample input records and relevant output record you are expecting. |
|
Back to top |
|
|
Ambili S
Active User
Joined: 06 Sep 2007 Posts: 112 Location: India
|
|
|
|
Hi Escapa
Here's what i wanted to say. The records that i want are the ones which are last in the sequence or the only ones . I wanted to know , if there's any way i can check for the last entry or only entry and append/push a charcter say 'Y' at a certain position and then include those records which have Y.
C1 100
C1 100
C1 500
C1 550
C1 500 Y
C2 250
C2 550
C2 900 Y
C3 800 Y
C4 900 Y
C5 100
C5 150 Y |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
Quote: |
append/push a charcter say 'Y' at a certain position and then include those records which have Y. |
But why? Frank has given you an answer that does not require such complications. |
|
Back to top |
|
|
Escapa
Senior Member
Joined: 16 Feb 2007 Posts: 1399 Location: IL, USA
|
|
|
|
Nic Clouston wrote: |
Quote: |
append/push a charcter say 'Y' at a certain position and then include those records which have Y. |
But why? Frank has given you an answer that does not require such complications. |
Nic, it appears poster has changed his\her requirement now... |
|
Back to top |
|
|
hailashwin
New User
Joined: 16 Oct 2008 Posts: 74 Location: Boston
|
|
|
|
I could manage only a two step process to get what was asked for. If you are not bothered about the order of the 2 byte codes, you can skip the second step.
Code: |
//STEP010 EXEC PGM=SORT
//SYSUDUMP DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SORTIN DD *
C1 100
C1 100
C1 500
C1 550
C1 500
C2 250
C2 550
C2 900
C3 800
C4 900
C5 100
C5 150
//SORTOUT DD DSN=DSN1,
// DISP=(NEW,CATLG,DELETE),
// UNIT=(SYSDA),SPACE=(CYL,(350,35),RLSE),
// DCB=(MODLDSCB,RECFM=FB,BLKSIZE=0,BUFNO=25)
//*
//SYSIN DD *
SORT FIELDS=(1,2,CH,A,90,8,ZD,D)
INREC OVERLAY=(90:SEQNUM,8,ZD,RESTART=(1,2))
OUTREC OVERLAY=(100:SEQNUM,8,ZD,RESTART=(1,2))
OUTFIL IFTHEN=(WHEN=(100,8,ZD,EQ,1),OVERLAY=(8:C'Y'))
//*
//STEP020 EXEC PGM=SORT
//SYSUDUMP DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=DSN1,DISP=SHR
//SORTOUT DD DSN=DSN2,
// DISP=(NEW,CATLG,DELETE),
// UNIT=(SYSDA),SPACE=(CYL,(350,35),RLSE),
// DCB=(MODLDSCB,RECFM=FB,BLKSIZE=0,BUFNO=25)
//*
//SYSIN DD *
SORT FIELDS=(1,2,CH,A,90,8,ZD,A)
OUTREC BUILD=(1,8,72X)
//*
|
Thanks,
Ashwin. |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
Quote: |
Nic, it appears poster has changed his\her requirement now |
No...the Y was mentioned in the first post. My understnding is that he wants the Y to identify the last/only record in the set so that he can then pull off the records with the Y. But you do not need that as Frank has shown. |
|
Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
|
|
|
|
Quote: |
I wanted to know if there is any way to opt for selective records by pushing a char say 'Y' . I was able to achieve the output but , in SORT is there any way we can select records by overlaying a id or character (something like marking the records i want ). |
DFSORT does not have any built-in feature for marking the last record with the same key unless the last key can be identified by something in the record (e.g. 'L' in position 1). But I really don't see why marking and deleting would be preferable to using SELECT with LAST. Of course, you could sort the records in reverse order, mark the first record (which would then be the last record) and then delete it, but what would be the point of that? |
|
Back to top |
|
|
Ambili S
Active User
Joined: 06 Sep 2007 Posts: 112 Location: India
|
|
|
|
Hi Frank
That's what i wanted to know if latest DFSORT has any such feature. Thanks for providing the details.
Thanks
Ambili |
|
Back to top |
|
|
|