Reformat Data



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Reformat Data

Postby Farhaan4mf » Mon Jul 17, 2017 1:54 pm

Hello -
I have a file where I need to reformat data in three fields Group# Member# and Agent# . Please let me know if it can be done by sort utility.

Currently These fields are left aligned with no prefix (1st 3rd and 4th fields)
----+----1----+----2----+----3
*****************************
1000   ;000;1         ;123   ;
1000101;000;2         ;24766 ;
102375 ;000;3         ;965864;
1000   ;000;4         ;965864;
1000   ;000;6         ;6475  ;
1000   ;000;7         ;96586 ;
1000   ;000;7         ;55    ;
1000   ;000;7         ;965864;
100009 ;000;2         ;123   ;
100009 ;000;2         ;24766 ;
100013 ;000;1         ;965864;
100013 ;000;2         ;965864;
100013 ;000;3         ;6475  ;
100013 ;000;5         ;96586 ;
100014 ;000;1         ;55    ;
100014 ;000;10        ;965864;


O/p should look like

----+----1----+----2----+----3
*****************************
0001000;000;000000001 ;000123;
1000101;000;000000002 ;024766;
0102375;000;000000003 ;965864;
0001000;000;000000004 ;965864;
0001000;000;000000006 ;006475;
0001000;000;000000007 ;096586;
0001000;000;000000007 ;000055;
0001000;000;000000007 ;965864;
0100009;000;000000002 ;000123;
0100009;000;000000002 ;024766;
0100013;000;000000001 ;965864;
0100013;000;000000002 ;965864;
0100013;000;000000003 ;006475;
0100013;000;000000005 ;096586;
0100014;000;000000001 ;000055;


group# has to be 7 character long (Prfix with 0 if the value is lesser than 7 digit)
Member# has to be 9 character long (Prfix with 0 if the value is lesser than 9 digit)
Agent# has to be 6 character long (Prfix with 0 if the value is lesser than 5 digit)
Farhaan4mf
 
Posts: 29
Joined: Mon May 13, 2013 4:53 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Reformat Data

Postby NicC » Mon Jul 17, 2017 2:21 pm

Look up PARSE to split the fields, SQZ to get rid of spaces and edit masks to format your data. I am not sure if this will do it but it is what I would look at first.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: Reformat Data

Postby Farhaan4mf » Mon Jul 17, 2017 3:16 pm

Hello NicC : You meant using REXX ?

Thanks
Farhaan4mf
 
Posts: 29
Joined: Mon May 13, 2013 4:53 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Reformat Data

Postby NicC » Mon Jul 17, 2017 3:44 pm

This is the DFSort forum. If I meant Rexx I would have said so. But...just for fun, look those things up in the Rexx language reference.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: Reformat Data

Postby Aki88 » Mon Jul 17, 2017 6:02 pm

Hello,

PARSEing is a solution, but it requires more code; since in this case the layout is fixed, a simpler approach would be using the 'TO=fo,LENGTH=n' function of DFSORT (there can be even more simpler solutions).
But before that, can you please confirm if the fields in question will always have numbers - only.

Solution:


----+----1----+----2----+----3
//SORTIN   DD *              
1000   ;000;1         ;123   ;
1000101;000;2         ;24766 ;
102375 ;000;3         ;965864;
1000   ;000;4         ;965864;
1000   ;000;6         ;6475  ;
1000   ;000;7         ;96586 ;
1000   ;000;7         ;55    ;
1000   ;000;7         ;965864;
100009 ;000;2         ;123   ;
100009 ;000;2         ;24766 ;
100013 ;000;1         ;965864;
100013 ;000;2         ;965864;
100013 ;000;3         ;6475  ;
100013 ;000;5         ;96586 ;
100014 ;000;1         ;55    ;
100014 ;000;10        ;965864;
/*                            
//SORTOUT  DD SYSOUT=*        
//SYSIN    DD *              
 INREC BUILD=(1,7,UFF,TO=ZD,LENGTH=7,
              C';',                  
              9,3,UFF,TO=ZD,LENGTH=3,
              C';',                  
              12,9,UFF,TO=ZD,LENGTH=9,
              C' ;',                  
              24,6,UFF,TO=ZD,LENGTH=6,
              C';')                  
 SORT FIELDS=COPY                    
/*                                    
 


Yields:


----+----1----+----2----+----3
0001000;000;000000001 ;000123;
1000101;000;000000002 ;024766;
0102375;000;000000003 ;965864;
0001000;000;000000004 ;965864;
0001000;000;000000006 ;006475;
0001000;000;000000007 ;096586;
0001000;000;000000007 ;000055;
0001000;000;000000007 ;965864;
0100009;000;000000002 ;000123;
0100009;000;000000002 ;024766;
0100013;000;000000001 ;965864;
0100013;000;000000002 ;965864;
0100013;000;000000003 ;006475;
0100013;000;000000005 ;096586;
0100014;000;000000001 ;000055;
0100014;000;000000010 ;965864;
 
Aki88
 
Posts: 381
Joined: Tue Jan 28, 2014 1:52 pm
Has thanked: 33 times
Been thanked: 36 times

Re: Reformat Data

Postby Farhaan4mf » Wed Jul 19, 2017 12:49 pm

Hello Aki88 -

Yes those fields will always have numbers only.

Thanks,
Minarul
Farhaan4mf
 
Posts: 29
Joined: Mon May 13, 2013 4:53 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Reformat Data

Postby Aki88 » Wed Jul 19, 2017 1:03 pm

Hello,

Then, the solution holds.

Hope this helps.
Aki88
 
Posts: 381
Joined: Tue Jan 28, 2014 1:52 pm
Has thanked: 33 times
Been thanked: 36 times

Re: Reformat Data

Postby Farhaan4mf » Wed Jul 19, 2017 1:26 pm

thanks a lot Aki88. It worked. But I do not want to reformat the 2nd fields (col 9-11 ) those values should remain as it is. How to retain those in o/p file without reformating

Regards,
Minarul
Farhaan4mf
 
Posts: 29
Joined: Mon May 13, 2013 4:53 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Reformat Data

Postby Aki88 » Wed Jul 19, 2017 1:57 pm

Hello,

Farhaan4mf wrote:..But I do not want to reformat the 2nd fields (col 9-11 ) those values should remain as it is...


It is a very small tweak, simply remove the 'UFF,TO=ZD,LENGTH=3' code piece for that bit-length.
Aki88
 
Posts: 381
Joined: Tue Jan 28, 2014 1:52 pm
Has thanked: 33 times
Been thanked: 36 times

Re: Reformat Data

Postby Farhaan4mf » Wed Jul 19, 2017 2:16 pm

Got it Aki88 and Thanks once again :)
Regards,
minarul
Farhaan4mf
 
Posts: 29
Joined: Mon May 13, 2013 4:53 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post