MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

DFSORT Exclude group of records by checking an amount field.

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
Magesh_J
Intermediate


Joined: 21 Jun 2014
Posts: 259
Topics: 54
PostPosted: Wed Aug 06, 2014 2:35 pm    Post subject: DFSORT Exclude group of records by checking an amount field. Reply with quote
Hi,

I have a list of account in a file

Account Length 3 Packed decimal and Amount Length 4 Packed decimal

The accounts are not in sequence.

Code:

Account          Amount
3/PS           4/PS         
(5-7)          (8-11)       
3------------- 4-------------
           383        -500.00
           383         250.00
           389        1438.20
           383         250.00
           389         619.92
           395        -500.00
           395         500.00
           400        -150.00
           389        1479.60
           400         200.00



I need to sum the amount based on account, if the sum is zero I need to exclude the entire accounts.

In the above input , Sum of the amount for the account number 383 and 395 is zero, I need those accounts to be excluded

Please advice.

expected output
Code:

Account          Amount
3/PS           4/PS         
(5-7)          (8-11)       
3------------- 4-------------
           389        1438.20
           389         619.92
           400        -150.00
           389        1479.60
           400         200.00


Thanks
Magesh
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12377
Topics: 75
Location: San Jose
PostPosted: Wed Aug 06, 2014 3:29 pm    Post subject: Reply with quote
Magesh_J,

Since you want to sum and still retain the duplicate keys you need to use the trick of using the same file twice for Joinkeys and get the desired results. I assumed your input is RECFM=FB and LRECL=80. I also assumed that your account has only positive values (389,390, 400...) if you have negative account numbers then we need to normalize the matching key using JNF1/JNF2. I will leave that for you to solve.

Code:

//STEP0100 EXEC PGM=SORT                                 
//SYSOUT   DD SYSOUT=*                                   
//INA      DD DISP=SHR,DSN=Your Input file
//INB      DD DISP=SHR,DSN=Same Input file once again
//SORTOUT  DD SYSOUT=*                                   
//SYSIN    DD *                                           
  OPTION COPY                                             
  JOINKEYS F1=INA,FIELDS=(5,3,A)                         
  JOINKEYS F2=INB,FIELDS=(1,3,A)                         
  REFORMAT FIELDS=(F1:1,80,F2:4,6)                       
  OMIT COND=(81,6,PD,EQ,0)                               
  INREC BUILD=(1,80)                                     
//*                                                       
//JNF2CNTL DD *                                           
  INREC BUILD=(5,3,2Z,8,4)                               
  SUM FIELDS=(4,6,PD)                                     
//*


The output will be sorted on the ACCOUNT, but if you need to retain the original order of the records then use the following Control cards.

Code:

//SYSIN    DD *                                           
  JOINKEYS F1=INA,FIELDS=(5,3,A)                         
  JOINKEYS F2=INB,FIELDS=(1,3,A)                         
  REFORMAT FIELDS=(F1:1,88,F2:4,6)                       
  OMIT COND=(89,6,PD,EQ,0)                               
  INREC BUILD=(1,88)                                     
  SORT FIELDS=(81,8,ZD,A)                                 
  OUTREC BUILD=(1,80)                                     
//*                                                       
//JNF1CNTL DD *                                           
  INREC OVERLAY=(81:SEQNUM,8,ZD)                         
//*                                                       
//JNF2CNTL DD *                                           
  INREC BUILD=(5,3,2Z,8,4)                               
  SUM FIELDS=(4,6,PD)                                     
//*                                                       

_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Magesh_J
Intermediate


Joined: 21 Jun 2014
Posts: 259
Topics: 54
PostPosted: Thu Aug 07, 2014 1:41 am    Post subject: Reply with quote
This is brilliance and awesome... I was doing with two steps, 1st sum the value and second step using join key... never had a thought to use same input file.

Great idea..

Thank you very much.

Regards,
Magesh
Back to top
View user's profile Send private message
ironponygrl
Beginner


Joined: 22 Mar 2013
Posts: 21
Topics: 7
Location: Fort Worth, TX
PostPosted: Mon Aug 18, 2014 6:07 pm    Post subject: kolusu is a genius w/dfsort ... u can put that in the bank Reply with quote
Magesh_J wrote:
This is brilliance and awesome... I was doing with two steps, 1st sum the value and second step using join key... never had a thought to use same input file.

Great idea..

Thank you very much.

Regards,
Magesh
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group