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

How to sum a particular field from a input dataset


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

New User


Joined: 06 Jan 2006
Posts: 12

PostPosted: Fri Jan 06, 2006 10:30 am
Reply with quote

how to sum the particular field from a input dataset using jcl

Title changed to "how to sum a particular field from a input dataset using jcl" : Priyesh.
Back to top
View user's profile Send private message
gram

New User


Joined: 06 Jan 2006
Posts: 12

PostPosted: Fri Jan 06, 2006 10:36 am
Reply with quote

Code:
//xxxxxxxx JOB (NI80),'XXX',CLASS=A,NOTIFY=xxxxxxx,MSGCLASS=X
//STEP EXEC PGM=SORT                                         
//SORTIN DD DSN=xxxxxxx.TRG.EXAMP,DISP=SHR                   
//SORTOUT DD DSN=xxxxxxx.TRG.GG,DISP=OLD                     
//SYSIN DD *                                                 
  SORT FIELDS=(7,4,CH,A)                                     
  SUM FIELDS=(1,4,BI)                                         
/*                                                           
//SYSPRINT DD SYSOUT=*                                       
//SYSOUT   DD SYSOUT=*                                       
//*                                                           
//       


this is the jcl code i have given..please check it out
thanks
gram.
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1448
Location: Chicago, IL

PostPosted: Fri Jan 06, 2006 11:01 am
Reply with quote

What is the data format of oyur input file.

Can you paste one row.

Regards,

Priyesh.
Back to top
View user's profile Send private message
gram

New User


Joined: 06 Jan 2006
Posts: 12

PostPosted: Fri Jan 06, 2006 11:06 am
Reply with quote

input data

1111 nnnn
2222 dddd



regards
gram
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1448
Location: Chicago, IL

PostPosted: Fri Jan 06, 2006 11:19 am
Reply with quote

Use SUM FIELDS=(1,4,ZD) instead.

Regards,

Priyesh.
Back to top
View user's profile Send private message
gram

New User


Joined: 06 Jan 2006
Posts: 12

PostPosted: Fri Jan 06, 2006 4:08 pm
Reply with quote

thanks
gram
Back to top
View user's profile Send private message
gram

New User


Joined: 06 Jan 2006
Posts: 12

PostPosted: Fri Jan 06, 2006 4:09 pm
Reply with quote

I tried giving as ZD format but its not working
Back to top
View user's profile Send private message
fixdoubts

New User


Joined: 21 Oct 2005
Posts: 54

PostPosted: Fri Jan 06, 2006 4:16 pm
Reply with quote

Hi,

i have doubt here you said you want to sum a particular field is it that
you want that sum on the key fields you have used in your case
its (7,4,CH,A) so

if the data is like

1111 aaaa
2222 bbbb
3333 bbbb

the output you will get is

1111 aaaa
5555 bbbb

is it what you wanted...

i thought you wanted the total sum 6666.

Regards,
Back to top
View user's profile Send private message
gram

New User


Joined: 06 Jan 2006
Posts: 12

PostPosted: Fri Jan 06, 2006 5:35 pm
Reply with quote

hi Fix,

i wan the total sum only

i/p data

1111 dddd
2222 hhhh
3333 kkkk

o/p data i wan is

1111 dddd
2222 hhhh
6666 kkkk
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: Fri Jan 06, 2006 9:38 pm
Reply with quote

gram,

You need to explain clearly what you want before anyone can help you.

Your latest input and output is quite confusing. You have records with different keys (dddd, hhhh, kkkk) and 1111, 2222 and 3333 for the sum fields. Somehow you want to end up with 1111, 2222 and 6666. 6666 is the sum of the three fields, but why is it overlaying the last sum field? You need to explain the rules you want to use to get what you want.
Back to top
View user's profile Send private message
gram

New User


Joined: 06 Jan 2006
Posts: 12

PostPosted: Sat Jan 07, 2006 12:38 pm
Reply with quote

hi Frank,


For example if i wan to add the total price of a particular field


i/p dataset

Dept Price
engl 100
math 300
engl 400
engl 300
engl 600
math 888
math 777
tami 100
tami 200

If i wan to add the price of dept 'engl' alone

we ll specify as

INCLUDE FIELDS=(1,4,CH,C'ENGL')
SORT=COPY
SUM FIELDS = (10,3,BI)




O/P Dataset will be

?????????????


Wher the sum value will appear???i thought it will overlay the last value of 'engl' dept with sum value..

Thanks for ur patience reply
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 Jan 07, 2006 8:55 pm
Reply with quote

With SUM and SORT (or MERGE), you get only one record with each key containing the SUM, e.g.

engl nnn
math nnn
...

If you use INCLUDE, SUM will only be applied to the records you keep.

If you only have one key, you could use COPY with TOTAL in OUTFIL TRAILER1 to get the total rather than using SORT with SUM, although you can use SORT with SUM if you want to.

Your example has all kinds of problems (syntax errors, SUM isn't allowed with COPY, you can't sum a 3-byte BI field - it has to be 2, 4 or 8 bytes, but it's probably a 3-byte ZD field and will overflow, etc). I can't help you until I understand what you're trying to do.

So let me ask some questions:

1) What does your sum field really look like? Is it really a 3-byte BI value or is it a 3-byte ZD value or what? What does it look like in hex?

2) Do you just want the total for one key (e.g. 'ENGL') or for each key?

3) Do you want your output to have one record with the key and sum or do you want the output in some other form? If in some other form, what exactly do you want it to look like?

Actually, I'd say the best thing for you to do is go through "z/OS DFSORT: Getting Started". It's an excellent tutorial, with lots of examples, that will show you how to use DFSORT, DFSORT's ICETOOL and DFSORT Symbols. You can access it online, along with all of the other DFSORT books, from:

Use [URL] BBCode for External Links

That would help you figure out how to do whatever it is you want to do yourself now and in the future.
Back to top
View user's profile Send private message
gram

New User


Joined: 06 Jan 2006
Posts: 12

PostPosted: Tue Jan 10, 2006 5:42 pm
Reply with quote

hi frank,

thanks for ur patience ....please send me an example for summing as with ur examples i will check it out.

This is the way i have given

Code:

                               
//STEP EXEC PGM=SORT                                               
//SORTIN DD DSN=TSOXXXX.TRG.KK,DISP=SHR                           
//SORTOUT DD DSN=TSOGRAM.TRG.TT,DISP=SHR                           
//SYSIN DD *                                                       
  INCLUDE COND=(1,7,CH,EQ,C'ENGLISH')                             
  SORT FIELDS=(1,7,CH,A)                                           
  SUM FIELDS=(9,2,BI)                                             
/*                                                                 
//SYSPRINT DD SYSOUT=*                                             
//SYSOUT   DD SYSOUT=*                                             
//*                                                               


input dataset

ENGLISH 10
ENGLISH 30
ENGLISH 40
ENGLISH 10
XXXXXX 45
LISHENG 90
TTTTTTT 80

The output i wan is

ENGLISH 90


AS A SINGLE RECORD

REGARDS
Gram
Back to top
View user's profile Send private message
fixdoubts

New User


Joined: 21 Oct 2005
Posts: 54

PostPosted: Tue Jan 10, 2006 5:51 pm
Reply with quote

Hi,

Then the output will be

ENGLISH 90

i think your JCL works perfectly fine.

Regards,
Back to top
View user's profile Send private message
gram

New User


Joined: 06 Jan 2006
Posts: 12

PostPosted: Tue Jan 10, 2006 6:03 pm
Reply with quote

hi fix,

right from the begining like this only tried with various examples but its not working...

thanks
Back to top
View user's profile Send private message
fixdoubts

New User


Joined: 21 Oct 2005
Posts: 54

PostPosted: Tue Jan 10, 2006 7:59 pm
Reply with quote

Hi,

i think frank is right there might be some mismatchs in the data type

i tried your code and its working fine

//STEP EXEC PGM=SORT
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SORTIN DD *
ENGLISH 10
ENGLISH 20
ENGLISH 30
MATHS 30
MATHS 30
/*
//SORTOUT DD SYSOUT=*
//SYSIN DD *
INCLUDE COND=(1,7,CH,EQ,C'ENGLISH')
SORT FIELDS=(1,7,CH,A)
SUM FIELDS=(09,2,ZD)
/*

the output am gettin is

ENGLISH 60

if you could show us the record layout it would be easier

Regards,
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: Tue Jan 10, 2006 9:38 pm
Reply with quote

Gram,

If you want me to help you, you need to answer the first question I asked in my previous post:

1) What does your sum field really look like? Is it really a 3-byte BI value or is it a 3-byte ZD value or what? What does it look like in hex?

Also, what is the starting position of the sum field?

Quote:
right from the begining like this only tried with various examples but its not working...


It would help if you'd tell us in what way it isn't working. Is it getting an error message, or giving you the "wrong" output (show what you're getting) or something else?

I suspect your sum field is really not a 2-byte BI field, but I can't know that unless you show me what it looks like. If you don't know, you can use a DFSORT job like this to display the data in hex, and post the SORTOUT output:

Code:

//S1 EXEC PGM=ICEMAN
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=...  input file
//SORTOUT DD SYSOUT=*
   OPTION COPY,STOPAFT=5
   OUTREC FIELDS=(p,m,HEX)
/*


Use the starting position of the sum field for p and the length of the sum field for m.
Back to top
View user's profile Send private message
gram

New User


Joined: 06 Jan 2006
Posts: 12

PostPosted: Wed Jan 11, 2006 10:04 am
Reply with quote

Hi Frank & Fix

1)Now its working when i changed the data type BI to ZD.

i have mentioned below the way i coded

//TSOGRRRR JOB (NI80),'XXX',CLASS=B,NOTIFY=TSOGRRR,MSGCLASS=X
//STEP EXEC PGM=SORT
//SORTIN DD DSN=TSOGRRR.TRG.MM,DISP=SHR
//SORTOUT DD DSN=TSOGRRR.TRG.GG,DISP=OLD
//*KK DD DSN=TSOGRRR.TRG.KK,DISP=OLD
//SYSIN DD *
INCLUDE COND=(1,7,CH,EQ,C'ENGLISH')
SORT FIELDS=(1,7,CH,A)
SUM FIELDS=(9,2,ZD)
/*
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//

i/p data set

ENGLISH 20
ENGLISH 30
ENGLISH 10
ENGLISH 30

o/p data set

english 90

Now i am getting correct answer

2)
As Frank said i got the hexa value for the sum fields

//TSOGRRRR JOB (NI80),'XXX',CLASS=B,NOTIFY=TSOGRRR,MSGCLASS=X
//STEP EXEC PGM=SORT
//SORTIN DD DSN=TSOGRRR.TRG.MM,DISP=SHR
//SORTOUT DD DSN=TSOGRRR.TRG.GG,DISP=OLD
//*KK DD DSN=TSOGRRR.TRG.KK,DISP=OLD
//SYSIN DD *
SORT FIELDS=COPY
OUTREC FIELDS=(9,2,HEX)
/*
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//


i/p data set


ENGLISH 20
ENGLISH 30
ENGLISH 10
ENGLISH 30

o/p data set

F2F0
F3F0
F1F0
F3F0

3)Why its not working wen i give BI
I want to know the mistake i have done before.....

Thank a loot
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: Wed Jan 11, 2006 9:28 pm
Reply with quote

Quote:
Why its not working wen i give BI
I want to know the mistake i have done before.....


BI and ZD are different representations of numeric values. You have to use the one that matches your actual data to get the correct result.

For example, decimal 10 is X'F1F0' or X'F1C0' for ZD, but X'000A' for BI.

For more information on DFSORT formats, see:

publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ICE1CA10/C.1?SHELF=&DT=20050222160456&CASE=
Back to top
View user's profile Send private message
gram

New User


Joined: 06 Jan 2006
Posts: 12

PostPosted: Thu Jan 12, 2006 9:45 am
Reply with quote

hi Frank,

Thanks a lot.
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 Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts FINDREP - Only first record from give... DFSORT/ICETOOL 3
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
No new posts Map Vols and Problem Dataset All Other Mainframe Topics 2
No new posts Allocated cylinders of a dataset DB2 12
Search our Forums:

Back to Top