SORT help - to split by delimiters
Hi, I have a requirement which states like this: Given records like 1~ABC~99.99~12345 123~AB~9.99~567 34~c~1.99~345 where there are 4 fields in each record, each separated by a '~'. The third field is the price field, which i need to sum up. I wont know the starting position / length of the fields. Is there any way that i can sum up the price, using Sort or DYL, without having to write a COBOL program using UNSTRING? Thanks in advance, Ramya -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: SORT help - to split by delimiters
On Aug 6, 2005, Ramya Ramaswamy wrote: > I have a requirement which states like this: > > Given records like > > 1~ABC~99.99~12345 > 123~AB~9.99~567 > 34~c~1.99~345 > > where there are 4 fields in each record, each separated by a '~'. The third > field is the price field, which i need to sum up. I wont know the starting > position / length of the fields. Is there any way that i can sum up the > price, using Sort or DYL, without having to write a COBOL program using > UNSTRING? You can now do this with the new PARSE function available with z/OS DFSORT V1R5 PTF UK90007 or DFSORT R14 PTF UK90006 (April, 2006), as shown in the DFSORT job below. I assumed your input file has RECFM=FB and LRECL=80, and that you want a trailer with the total of the price values, but you can change the job appropriately for other attributes and output requirements. You didn't say what the maximum price value was, so I added another record with a larger price field for illustration. //S1EXEC PGM=ICEMAN //SYSOUTDD SYSOUT=* //SORTIN DD * 821~XYZ~25,821.03~5 1~ABC~99.99~12345 123~AB~9.99~567 34~c~1.99~345 /* //SORTOUT DD SYSOUT=* //SYSINDD* OPTION COPY * Ignore first ~ delimited field. INREC PARSE=(%=(ENDBEFR=C'~'), * Ignore second ~ delimited field. %=(ENDBEFR=C'~'), * Extract third ~ delimited field into * a 9-byte %00 fixed parsed field. %00=(ENDBEFR=C'~',FIXLEN=9)), * Add %00 after end of each record so we can total it. OVERLAY=(81:%00) * Remove ANSI carriage control characters from output. OUTFIL REMOVECC, * Write trailer record with total of %00 values - * use UFF format to handle comma and decimal point. TRAILER1=(/,'Sum of Prices: ', TOT=(81,9,UFF,EDIT=(II,IIT.TT))), * Remove %00 value from each record. BUILD=(1,80) /* SORTOUT would be RECFM=FB and LRECL=80 with these output records: 821~XYZ~25,821.03~5 1~ABC~99.99~12345 123~AB~9.99~567 34~c~1.99~345 Sum of Prices: 25,933.00 For complete details on DFSORT's new PARSE function, see: www.ibm.com/servers/storage/support/software/sort/mvs/peug/ Frank Yaeger - DFSORT Team (IBM) Specialties: PARSE, JFY, SQZ, ICETOOL, IFTHEN, OVERLAY, Symbols, Migration => DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort/ -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: SORT help - to split by delimiters
Perhaps you could run the following script in batch: #!/usr/bin/awk -f BEGIN \ { FS = ~ accum = 0 } { accum += $3 } END \ { printf( "sum of prices = %d\n", accum) } > -Original Message- > From: Ramya Ramaswamy [mailto:[EMAIL PROTECTED] > Sent: Saturday, August 6, 2005 04:13 PM > To: IBM-MAIN@BAMA.UA.EDU > Subject: SORT help - to split by delimiters > > Hi, > > I have a requirement which states like this: > > Given records like > > 1~ABC~99.99~12345 > 123~AB~9.99~567 > 34~c~1.99~345 > > where there are 4 fields in each record, each separated by a '~'. The third > field is the price field, which i need to sum up. I wont know the starting > position / length of the fields. Is there any way that i can sum up the > price, using Sort or DYL, without having to write a COBOL program using > UNSTRING? > > Thanks in advance, > Ramya > > -- > For IBM-MAIN subscribe / signoff / archive access instructions, > send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO > Search the archives at http://bama.ua.edu/archives/ibm-main.html > -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: SORT help - to split by delimiters
> -Original Message- > From: IBM Mainframe Discussion List On Behalf Of Ramya Ramaswamy > > Hi, > > I have a requirement which states like this: > > Given records like > > 1~ABC~99.99~12345 > 123~AB~9.99~567 > 34~c~1.99~345 > > where there are 4 fields in each record, each separated by a > '~'. The third field is the price field, which i need to sum > up. I wont know the starting position / length of the fields. > Is there any way that i can sum up the price, using Sort or > DYL, without having to write a COBOL program using UNSTRING? You could write an assembler E15 exit program using TRT Not familiar with DYL, but AFAIK the SORT utilities require the SUMmed fields to be the same length, format and offset in every record. -jc- -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: SORT help - to split by delimiters
In a message dated 8/8/2005 7:05:40 A.M. Central Standard Time, [EMAIL PROTECTED] writes: You could write an assembler E15 exit program using TRT Not familiar with DYL, but AFAIK the SORT utilities require the SUMmed fields to be the same length, format and offset in every record. >> Depending on how big it is can EDIT ch '~' to Lazy-L's and turn tabs on. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: SORT help - to split by delimiters
There's a crude way to do this by aligning the data in a series of temp files, i.e. OUTREC OUTFIL=OUT1, INCLUDE=(8,3,CH,EQ,C'99~'), WHEN COL 8 IS THE DESIRED 99~ OUTREC=(11,5) numeric data following the ~ repeat this block of lines for every mutation of where ~ occurs. write an additional OUTFIL for each mutation. then concat each file into a SUM step. Hey, the price is right.. -Original Message- From: IBM Mainframe Discussion List [mailto:[EMAIL PROTECTED] On Behalf Of Chase, John Sent: Monday, August 08, 2005 7:05 AM To: IBM-MAIN@BAMA.UA.EDU Subject: Re: SORT help - to split by delimiters > -Original Message- > From: IBM Mainframe Discussion List On Behalf Of Ramya Ramaswamy > > Hi, > > I have a requirement which states like this: > > Given records like > > 1~ABC~99.99~12345 > 123~AB~9.99~567 > 34~c~1.99~345 > > where there are 4 fields in each record, each separated by a '~'. The > third field is the price field, which i need to sum up. I wont know > the starting position / length of the fields. > Is there any way that i can sum up the price, using Sort or DYL, > without having to write a COBOL program using UNSTRING? You could write an assembler E15 exit program using TRT Not familiar with DYL, but AFAIK the SORT utilities require the SUMmed fields to be the same length, format and offset in every record. -jc- -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: SORT help - to split by delimiters
> -Original Message- > From: IBM Mainframe Discussion List On Behalf Of tony babonas > > There's a crude way to do this by aligning the data in a > series of temp files, i.e. > > OUTREC OUTFIL=OUT1, >INCLUDE=(8,3,CH,EQ,C'99~'), WHEN COL 8 IS > THE DESIRED 99~ >OUTREC=(11,5) numeric data > following the ~ > > repeat this block of lines for every mutation of where ~ > occurs. write an additional OUTFIL for each mutation. then > concat each file into a SUM step. > > Hey, the price is right.. But you'd "lose" any and all records with a price field that doesn't end in '99~' -jc- [ snip ] > > I have a requirement which states like this: > > > > Given records like > > > > 1~ABC~99.99~12345 > > 123~AB~9.99~567 > > 34~c~1.99~345 > > > > where there are 4 fields in each record, each separated by > a '~'. The > > third field is the price field, which i need to sum up. ... -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: SORT help - to split by delimiters
Nah, Yaeger is always one step ahead. I should have added: OUTREC OUTFIL=ORPHANS,SAVE -Original Message- From: IBM Mainframe Discussion List [mailto:[EMAIL PROTECTED] On Behalf Of Chase, John Sent: Tuesday, August 09, 2005 6:42 AM To: IBM-MAIN@BAMA.UA.EDU Subject: Re: SORT help - to split by delimiters > -Original Message- > From: IBM Mainframe Discussion List On Behalf Of tony babonas > > There's a crude way to do this by aligning the data in a series of > temp files, i.e. > > OUTREC OUTFIL=OUT1, >INCLUDE=(8,3,CH,EQ,C'99~'), WHEN COL 8 IS > THE DESIRED 99~ >OUTREC=(11,5) numeric data > following the ~ > > repeat this block of lines for every mutation of where ~ occurs. > write an additional OUTFIL for each mutation. then concat each file > into a SUM step. > > Hey, the price is right.. But you'd "lose" any and all records with a price field that doesn't end in '99~' -jc- [ snip ] > > I have a requirement which states like this: > > > > Given records like > > > > 1~ABC~99.99~12345 > > 123~AB~9.99~567 > > 34~c~1.99~345 > > > > where there are 4 fields in each record, each separated by > a '~'. The > > third field is the price field, which i need to sum up. ... -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: SORT help - to split by delimiters
Tony Babonas wrote: >Nah, Yaeger is always one step ahead. > >I should have added: > >OUTREC OUTFIL=ORPHANS,SAVE Tony, That would be: OUTFIL FNAMES=ORPHANS,SAVE But I'm guessing that John's point is that the .99 values are only an example and other values (.00 to .99) would actually be present, and those values would end up NOT being aligned by the .99 conditions. If there really only were .99 values, then DFSORT's IFTHEN function could be used to align everything in one pass and one output data set rather than creating multiple output data sets that would need to be combined (replacing multiple OUTFIL INCLUDEs is one of the more efficient uses of IFTHEN). IFTHEN can be used to do this kind of alignment in limited situations, but in this case it appears there's too much variability in the delimited field layouts for that to be practical. Although if I knew all of the possibilities for the record layouts, I might be able to come up with an IFTHEN solution. Frank Yaeger - DFSORT Team (IBM) Specialties: ICETOOL, IFTHEN, OVERLAY, Symbols, Migration => DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort/ -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: SORT help - to split by delimiters
Thanks for the clarification, FY, that's what I get for reading the list from home without my trusty office PC at hand Based on the limited snippet of sample records, this is theoretically achievable if there is not more than 1 ~ character per record. Having no qualms of being accused of crudeness in manipulating such data, we have some real life apps where I read and compare every byte of the record for the desired character, 'outfil-ing' each into a separate dataset, then re-assembling the whole lot in the second pass. The A-10 Warthog isn't pretty either, but it sure does the job. tb -Original Message- From: IBM Mainframe Discussion List [mailto:[EMAIL PROTECTED] On Behalf Of Frank Yaeger Sent: Tuesday, August 09, 2005 7:12 PM To: IBM-MAIN@BAMA.UA.EDU Subject: Re: SORT help - to split by delimiters Tony Babonas wrote: >Nah, Yaeger is always one step ahead. > >I should have added: > >OUTREC OUTFIL=ORPHANS,SAVE Tony, That would be: OUTFIL FNAMES=ORPHANS,SAVE But I'm guessing that John's point is that the .99 values are only an example and other values (.00 to .99) would actually be present, and those values would end up NOT being aligned by the .99 conditions. If there really only were .99 values, then DFSORT's IFTHEN function could be used to align everything in one pass and one output data set rather than creating multiple output data sets that would need to be combined (replacing multiple OUTFIL INCLUDEs is one of the more efficient uses of IFTHEN). IFTHEN can be used to do this kind of alignment in limited situations, but in this case it appears there's too much variability in the delimited field layouts for that to be practical. Although if I knew all of the possibilities for the record layouts, I might be able to come up with an IFTHEN solution. Frank Yaeger - DFSORT Team (IBM) Specialties: ICETOOL, IFTHEN, OVERLAY, Symbols, Migration => DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort/ -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: SORT help - to split by delimiters
> -Original Message- > From: IBM Mainframe Discussion List On Behalf Of tony babonas > > Thanks for the clarification, FY, that's what I get for > reading the list from home without my trusty office PC at hand > Based on the limited snippet of sample records, this is > theoretically achievable if there is not more than 1 ~ > character per record. Having no qualms of being accused of > crudeness in manipulating such data, we have some real life > apps where I read and compare every byte of the record for > the desired character, 'outfil-ing' each into a separate > dataset, then re-assembling the whole lot in the second pass. Blech! That's what the E15 exit point and the assembler TRT instruction are for. > The A-10 Warthog isn't pretty either, but it sure does the job. Yeah, but it doesn't carry just one bullet, fire it, return to base for another, return to the battlefield, etc. -jc- -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: SORT help - to split by delimiters
All reasonable answers, Frank, but the "awk" script proposed in one of the replies to the OP's question will do it right now with no fuss, no muss, problem solved already. Floating fields like the OP gave as an example are perfect targets for regular expression pattern matching. What you could seriously consider as a genuine enhancement would be regular expression pattern matching (like "awk" and "perl") for SORT FIELDS, as opposed to fixed-position matching. IIRC, there is even a GNU library you could link in to perform the matching for you (regexp, if memory serves, but I could be wrong about that name). Just a thought. Peter -Original Message- From: IBM Mainframe Discussion List [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 09, 2005 8:14 PM To: IBM-MAIN@BAMA.UA.EDU Subject: Re: SORT help - to split by delimiters Tony Babonas wrote: >Nah, Yaeger is always one step ahead. > >I should have added: > >OUTREC OUTFIL=ORPHANS,SAVE Tony, That would be: OUTFIL FNAMES=ORPHANS,SAVE But I'm guessing that John's point is that the .99 values are only an example and other values (.00 to .99) would actually be present, and those values would end up NOT being aligned by the .99 conditions. If there really only were .99 values, then DFSORT's IFTHEN function could be used to align everything in one pass and one output data set rather than creating multiple output data sets that would need to be combined (replacing multiple OUTFIL INCLUDEs is one of the more efficient uses of IFTHEN). IFTHEN can be used to do this kind of alignment in limited situations, but in this case it appears there's too much variability in the delimited field layouts for that to be practical. Although if I knew all of the possibilities for the record layouts, I might be able to come up with an IFTHEN solution. Frank Yaeger - DFSORT Team (IBM) _ This message and any attachments are intended only for the use of the addressee and may contain information that is privileged and confidential. If the reader of the message is not the intended recipient or an authorized representative of the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by e-mail and delete the message and any attachments from your system. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html