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 

Convert Rows to Columns

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


Joined: 09 Aug 2010
Posts: 31
Topics: 11

PostPosted: Fri Mar 13, 2015 1:00 am    Post subject: Convert Rows to Columns Reply with quote

I have a input like this
Code:

111A
222AB
333ABC
444ABC
555ABCD
666BCD
777BDEA

Where the first 3 bytes are the account numbers and the remaining is account preferences. I want to convert it like a transpose function.

I would like to get a output like this. I have used '|' as a delimiter between A and 1 and 111,222,333,444,555 and so on.
Code:

A|1|111,222,333,444,555
A|4|777
B|1|666,777
B|2|222,333,444,555
C|2|666
D|2|777
C|3|333,444,555
D|3|666
E|3|777
D|4|555

More explanation :
Preference A is the 1st choice for accounts 111,222,333,444 and 555.
Preference B is the 1st choice for accounts 666 and 777.
Preference B is the 2nd choice for accounts 222,333,444 and 555.
Preference C is the 2nd choice for account 666.
Preference D is the 2nd choice for account 777.
Preference C is the 3rd choice for accounts 333,444 and 555
and so on..

Is this achievable using Dfsort or Syncsort?
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Fri Mar 13, 2015 11:19 am    Post subject: Reply with quote

ed.sam13,

What is the maximum number of Account preferences? You show A,B,C,D,E... So do you have a limit? If so what is the limit?

I think it can be done but would 2 passes of data(assuming you got a Fixed block input file). What is the format of the account number and Account preferences? I am sure they are not what you show here. Are any of the fields either binary or Packed?

Please answer the following questions.

1. What is the LRECL and RECFM of the Input file?
2. Post the layout of the file contents i.e COBOL Layout if possible.
3. What is the LRECL and RECFM of the Output file?
4. Post all the order of preferences for accounts. (C doesn't seem to have a 1st choice )
5. Last but not least please run the following JCL and show me the complete sysout, so that we can determine the level of DFSORT you have.

Code:

//STEP0100 EXEC PGM=SORT         
//SYSOUT   DD SYSOUT=*           
//SORTIN   DD *                 
//SORTOUT  DD SYSOUT=*           
//SYSIN    DD *                 
  OPTION COPY                   
//*

_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

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


Joined: 09 Aug 2010
Posts: 31
Topics: 11

PostPosted: Fri Mar 13, 2015 11:52 am    Post subject: Reply with quote

hi Kolusu,
Thanks for the reply.
None of the fields are packed.
Actually there is another field in the middle between ACCT-NO and ACCT-PREFERENCES which is NO-OF-PREFERENCES which shows the number of preferences for the account. I was not sure if that would be useful at all.

The following is the layout of the file
Code:
01 ACCT-PREFERENCES.
   05 ACCT-NO           PIC X(03).
   05 FILLER            PIC X(01).
   05 NO-OF-PREFERENCES PIC 9(04).
   05 FILLER            PIC X(01).
   05 ACCT-PREFERENCES  PIC X(01) OCCURS 255 TIMES.

So the Record length of the input file is 264 bytes.
The output file is a FB file of 32760 bytes.

So the file is actually like this. Positions 5,4 shows the number of preferences.
Code:

111 0001 A
222 0002 AB
333 0003 ABC
444 0003 ABC
555 0004 ABCD
666 0003 BCD
777 0004 BDEA

Not sure if I follow your question 4 correctly. If there is a record like
Code:

888 0001 C

then C will be the 1st choice for 888.

We actually have syncsort 2.1 in our shop.

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


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Fri Mar 13, 2015 12:06 pm    Post subject: Reply with quote

ed.sam13 wrote:
We actually have syncsort 2.1 in our shop.


That is a bummer. I'm a DFSORT developer. DFSORT and Syncsort are competitive products. I'm happy to answer questions on DFSORT and DFSORT's ICETOOL, but I don't answer questions on Syncsort.

However here is a rough draft to get the results.

1. Use RESIZE or OUTFIL "/" to build the records with ACCT-NO and Account preferences eliminating the Space preference records.

2. Sort the file from step 1 on the account preference and account number.

3. Using OUTREC IFTHEN you will perform the validation of preferences and build the record moving the account number to the specified position.

4. Using OUTFIL Sections with Trailer3 build the desired record.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

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


Joined: 09 Aug 2010
Posts: 31
Topics: 11

PostPosted: Fri Mar 13, 2015 2:09 pm    Post subject: Reply with quote

Ok. I came upto this

Code:
A 0001 111
A 0001 222
A 0001 333
A 0001 444
A 0001 555
A 0004 777
B 0001 666
B 0001 777
B 0002 222
B 0002 333
B 0002 444
B 0002 555
C 0001 888
C 0002 666
C 0003 333
C 0003 444
C 0003 555
D 0002 777
D 0003 666
D 0004 555
E 0003 777


Now, when i use TRAILER3 and SECTIONS how do i get it like
A 0001 111,222,333,444,555,777

I have used aggregator functions in trailer3 but how do i concatenate values from multiple rows on trailer3? Is it possible at all?
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Fri Mar 13, 2015 2:30 pm    Post subject: Reply with quote

ed.sam13,

I am going to show it for 1 account and you can extrapolate it to other codes and validation. The best way to understand this job is to run 1 IFTHEN statement at a time and see how we are building the record.

Code:

//STEP0100 EXEC PGM=SORT                                           
//SYSOUT   DD SYSOUT=*                                             
//SORTIN   DD *                                                     
----+----1----+----2----+----3----+----4----+----5----+----6----+---
A 0001 111                                                         
A 0001 222                                                         
A 0001 333                                                         
A 0001 444                                                         
A 0001 555                                                         
A 0004 777                                                         
//SORTOUT  DD SYSOUT=*                                             
//SYSIN    DD *                                                     
  OPTION COPY                                                       
  OUTREC IFTHEN=(WHEN=INIT,                                         
          BUILD=(1,1,C'|',81:1,10,C',',SEQNUM,3,ZD,RESTART=(1,6))),
  IFTHEN=(WHEN=GROUP,BEGIN=(92,3,ZD,EQ,1),PUSH=(003:88,4)),         
  IFTHEN=(WHEN=GROUP,BEGIN=(92,3,ZD,EQ,2),PUSH=(007:88,4)),         
  IFTHEN=(WHEN=GROUP,BEGIN=(92,3,ZD,EQ,3),PUSH=(011:88,4)),         
  IFTHEN=(WHEN=GROUP,BEGIN=(92,3,ZD,EQ,4),PUSH=(015:88,4)),         
  IFTHEN=(WHEN=GROUP,BEGIN=(92,3,ZD,EQ,5),PUSH=(019:88,4)),         
  IFTHEN=(WHEN=GROUP,BEGIN=(92,3,ZD,EQ,6),PUSH=(023:88,4)),         
  IFTHEN=(WHEN=(92,3,ZD,EQ,1),OVERLAY=(007:074X)),                 
  IFTHEN=(WHEN=(92,3,ZD,EQ,2),OVERLAY=(011:070X)),                 
  IFTHEN=(WHEN=(92,3,ZD,EQ,3),OVERLAY=(015:066X)),                 
  IFTHEN=(WHEN=(92,3,ZD,EQ,4),OVERLAY=(019:062X)),                 
  IFTHEN=(WHEN=(92,3,ZD,EQ,5),OVERLAY=(023:058X))                   
                                                                   
  OUTFIL REMOVECC,NODETAIL,BUILD=(80X),                             
  SECTIONS=(81,6,                                                   
  TRAILER3=(1,80))                                                 
//*

_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

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


Joined: 09 Aug 2010
Posts: 31
Topics: 11

PostPosted: Fri Mar 13, 2015 3:07 pm    Post subject: Reply with quote

hi Kolusu, Thanks for your reply.
I was able to figure out pretty much the same thing using the splice command. But if there are 5000 accounts for which A is 1st preference then it makes this SORT card really complicated. Would you agree?

Just for this last step, a COBOL program or a REXX utility would be more simpler, I guess.

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


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Fri Mar 13, 2015 3:23 pm    Post subject: Reply with quote

ed.sam13 wrote:
But if there are 5000 accounts for which A is 1st preference then it makes this SORT card really complicated. Would you agree?
Ed


Ed,

Any Sort job that has more than 100 lines of control cards should be solved using a program. I would stay away from rexx. A Simple COBOL program is easier to maintain as well will have better performance when compared to interpreted Rexx exec.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
William Collins
Supermod


Joined: 03 Jun 2012
Posts: 437
Topics: 0

PostPosted: Fri Mar 13, 2015 5:03 pm    Post subject: Reply with quote

You have an OCCURS of 255, so, apart from one value, you have every possible bit-pattern. You have a binary field.

Why 32760 FB output? On tape? Do you mean the LRECL is 32760? You have at most 999 possible ACCT-NOs, each requiring four bytes, with the comma, so only need an LRECL of about 4000?
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