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 

Adding columns of Duplicate records together

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


Joined: 09 Apr 2007
Posts: 7
Topics: 2
Location: Waterloo, Ontario, Canada
PostPosted: Thu Jun 14, 2007 9:39 pm    Post subject: Adding columns of Duplicate records together Reply with quote
I am wondering if anyone can help me with figuring out a solution on how I can get this achieved.

I have a feed that contains thousands of records and I have to roll them up (summarize). The unique identifiers are the AGNTID, CAUSE, & CODE.

How it has to be rolled-up, is as follows:

If CAUSE=444, leave the record as is.
If CAUSE=888; then add the amounts together for a particular CODE and delete the duplicate records.

INPUT
Code:

AGNTID  CAUSE   CODE   AMOUNT
  4/PS   2/PS   2/PS     6/PS
11111111  444    141     -427
11111111  444    141      512
11111111  888    131     1325
11111111  888    131     -155
11111111  888    142       -1
11111111  888    142      -18
22222222  444    131      152
22222222  888    113       29
22222222  888    113        2
22222222  888    113   -11896
22222222  888    113     -566



EXPECTED OUTPUT
Code:

AGNTID  CAUSE   CODE   AMOUNT
  4/PS   2/PS   2/PS     6/PS
11111111  444    141     -427
11111111  444    141      512
11111111  888    131     1170
11111111  888    142      -19
22222222  444    131      152
22222222  888    113   -12431



I'm hoping to do this either though some type of sort/merge/??? utility or SAS. If I have to I will do it in COBOL but I don't want to go down that route since it has to get implemented ASAP!!!

Many thanks for your time to helping me out!!!
Back to top
View user's profile Send private message
krisprems
Beginner


Joined: 13 Dec 2006
Posts: 101
Topics: 4
Location: india
PostPosted: Fri Jun 15, 2007 3:25 am    Post subject: Reply with quote
DeX
which format is the amount(last field in your i/p) present in your file?
_________________
cHEERs
krisprems
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Fri Jun 15, 2007 6:34 am    Post subject: Reply with quote
DeX,

try this untested control cards. A brief explanation. we add an indicator at the end of the record for all of the records using ifthen and when the CAUSE is 888 then we make the indicator to be zero, so that we can sum all the duplicate records for 888 cause.

Code:

//SYSIN    DD  *                           
                                           
  INREC IFTHEN=(WHEN=INIT,                 
       OVERLAY=(17:SEQNUM,8,ZD)),         
        IFTHEN=(WHEN=(5,2,PD,EQ,888),     
       OVERLAY=(17:8C'0'))                 
                                           
  SORT FIELDS=(01,04,PD,A,                 
               05,02,PD,A,                 
               07,02,PD,A,                 
               17,08,ZD,A)                 
  SUM FIELDS=(09,06,PD)                   
                                           
  OUTREC BUILD=(01,16)                     
/*


Hope this helps...

Cheers

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


Joined: 09 Apr 2007
Posts: 7
Topics: 2
Location: Waterloo, Ontario, Canada
PostPosted: Fri Jun 15, 2007 2:35 pm    Post subject: Reply with quote
krisprems wrote:
DeX
which format is the amount(last field in your i/p) present in your file?



Sorry the agentid is only 6 characters wide.

Also, the file DCB=(RECFM=VB,LRECL=304,BLKSIZE=23412)

Code:

 VARS    FORMAT  DISPLAY 
                          ----+----10
AGNTID    S9(6)   123456  0246
                          135C
                          ----+----10
CAUSE      S999      888  88
                          8C
                          ----+----10
CODE       S999      142  12
                          4C
                          ----+----10
AMOUNT   S9(11)   172045  000705
+VE $                     00124C
                          ----+----10
AMOUNT   S9(11)  -235940  000390
-VE $                     00254D

Back to top
View user's profile Send private message
krisprems
Beginner


Joined: 13 Dec 2006
Posts: 101
Topics: 4
Location: india
PostPosted: Sat Jun 16, 2007 8:03 am    Post subject: Reply with quote
Dex

I tried this code(variation of kolusu's code, changed asper your layout),
i/p
Code:
----+----1----+----2----+----3--
111111    444    141         427
111111    444    141         512
111111    888    131        1325
111111    888    131         155
111111    888    142           1
111111    888    142          1J
222222    444    131         152
222222    888    113         029
222222    888    113          0D
222222    888    113       11896
222222    888    113         566


Sort Card
Code:
//SYSIN    DD  *                                                       
  INREC IFTHEN=(WHEN=INIT,             *PUT SEQUENCE TO ALL RECS       
       OVERLAY=(35:SEQNUM,8,ZD)),                                       
        IFTHEN=(WHEN=(11,3,ZD,EQ,888), *PUT '00000000' INSTEAD OF       
       OVERLAY=(35:8C'0'))             *SEQUENCE NUM, IF CAUSE IS '888'
  SORT FIELDS=(01,06,ZD,A,                                             
               11,03,ZD,A,                                             
               18,03,ZD,A,                                             
               35,08,ZD,A)                                             
*ADD AMOUNT                                                             
  SUM FIELDS=(22,11,ZD)                                                 
  OUTREC FIELDS=(1,34)                                                 
/*                                                                     

Sortout contains
Code:
---+----1----+----2----+----3---
111111    444    141         427
111111    444    141         512
111111    888    131 00000001480
111111    888    142 0000000001}
222222    444    131         152
222222    888    113 00000012495

_________________
cHEERs
krisprems
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