# Can this be done with DFSORT?

Author Message
Claes Norreen

Active User

Joined: 20 Dec 2005
Posts: 137
Location: Denmark

Posted: Thu Apr 16, 2015 1:00 pm

Hi,

I've got the following requirement, that I'd love DFSORT to do for me - although I realize I may need to use an application program..

Basically, I've got a list of streets (identified by an unique id) and housenumbers on that street. My requirement is to make a list of streets with housenumber intervals. Also, a record must appear for even houseno and another for odd. The trick being, that "holes" in the housenumbers may appear. Take these examples:

Input:
 Code: streetId  HouseNo 1            1 1            2 1            3 1            4 1            5 1            6 1            7 1            8

results in 2 records:
 Code: streetId  HouseNoFrom HouseNoTo  Even/Odd 1            1          7          O 1            2          8          E

whereas this input:
 Code: streetId  HouseNo 1            1 1            2 1            4 1            5 1            7 1            8

would result in these records (as 3 and 6 is not present in input):
 Code: streetId  HouseNoFrom HouseNoTo  Even/Odd 1             1          1          O 1             5          7          O 1             2          4          E 1             8          8          E

Hope this one will bring some joy to you! I welcome anything that can guide me in the right direction.

Note: The odd/even thing can be achieved using MOD - divide by 2 and see if the remainder is 0 or 1.
Bill Woodger

Moderator Emeritus

Joined: 09 Mar 2011
Posts: 7311
Location: Inside the Matrix

 Posted: Thu Apr 16, 2015 2:30 pm DFSORT can check the bits for odd/even :-) How much data is there? Is it safe to assume that there are no duplicate house-numbers (like for apartment blocks) or compound house-numbers like 3-5 (for a building which has consumed multiple adjacent numbers)? No sub-divided house-numbers, like 14, 14A, 14B for an old building sub-divided into apartments? No "house names" in place of the number (like Dun Roamin, Bill's Gaff)? Another way to put it, all correct, unique, numbers? Are they left-aligned? What is the maximum number that can exist? There are several ways to do it (almost certainly). Do you want for performance, clarity, or something else?
Claes Norreen

Active User

Joined: 20 Dec 2005
Posts: 137
Location: Denmark

 Posted: Thu Apr 16, 2015 2:37 pm Hi Bill, Thanks for your fast reply! (thought it was night in the US now?) Well, it's just Danish addresses, so there's not a whole lot of data - so clarity first, I guess There CAN be duplicate numbers - but only because of a letter (separate field) - nothing else. So we can have 14A, 14B and so on. We could remove duplicates easily using SELECT FIRST. Housenumbers in Denmark are numeric 3 - letters (separate field) A-Z. Looking forward to see some more!
RahulG31

Active User

Joined: 20 Dec 2014
Posts: 446
Location: USA

 Posted: Fri Apr 17, 2015 10:51 pm I believe this could be done but not that we should. per me, it would involve multiple sort steps (and a decent amount of logic as well). This is what I thought of doing: 1. Separate odd/even numbers in separate files by checking the last bit (as stated by bill). Let's consider the odd numbers file has input like this: 01 03 07 11 2. Take this file and see if the difference in consecutive numbers is greater than 2. There will be a separate step for doing this using JOINKEYS to get records like below: 01 03 07 A 11 A 3. Put the file in reverse order and do as in step 2 to get: 11 B 07 B 03 B 01 4. If the records are merged again with JOINKEYS then we would get: 01 A 03 B 07 A B 11 A B 5. 'A' signifies start of group and 'B' signifies end of group. If you PUSH what is present in records with 'A' (i.e. 01 for first record and so on..), you should get something like this: 01 A 01 03 B 01 07 A B 07 11 A B 11 6. A simple BUILD on this will give you the required numbers. So, it looks to me that writing an application program would be much simpler. I am curious to know a simpler solution. Waiting for reply from Bill. :-) .
Claes Norreen

Active User

Joined: 20 Dec 2005
Posts: 137
Location: Denmark

 Posted: Mon Apr 20, 2015 1:21 pm RahulG31, Thanks for your reply. However, I don't understand what you are trying to do - can you put some code for it maybe?
RahulG31

Active User

Joined: 20 Dec 2014
Posts: 446
Location: USA

Posted: Mon Apr 20, 2015 8:57 pm

Claes,

I am trying to create a group for the data in vertical format, so that you can get your desired output in horizontal format.

I am marking 'A' for the start of the group and 'B' for the end of group.
Where 'A' and 'B' both are present, that means that is the only element in the group e.g. 7 and 11 in my sample data.

The points I mentioned, are part of the idea on how to do it.

1. To identify odd/even:
 Code: //SYSIN DD *                                            OPTION COPY                                          OUTFIL FNAMES=EVEN,INCLUDE=(1,1,BI,EQ,B'.......0')   OUTFIL FNAMES=ODD,SAVE                              /*

2. There can be multiple ways to find the difference between consecutive numbers. It's upto you how you do it. I used 2 files, one with all my input records and other with 1 less record and Joined them based on sequence numbers to get:

0103
0307
0711

if you see here, for first record: 03 - 01 = 2
second record: 07 - 03 = 4
third record: 11 - 07 = 4

Place an 'A' where the difference is greater than 2 i.e.

0103
0307 A
0711 A

This is how you place 'A' or 'B':
 Code: //SYSIN DD *                                                           OPTION COPY                                                           INREC BUILD=(1,4,(3,2,ZD,SUB,1,2,ZD))                                 OUTREC IFTHEN=(WHEN=(5,15,ZD,GT,2),BUILD=(3,2,3:3X,6:C'B',7:74X)),          IFTHEN=(WHEN=(5,15,ZD,EQ,2),BUILD=(3,2,3:78X))                /*

Put 'A' and 'B' in different positions, so that you get data like this:
 Code: 01 A    03 B 07 A B 11 A B

Now you use WHEN=GROUP to identify 'A' as a start of group and 'B' as end of group and PUSH first 2 bytes. Something like:
 Code: OUTREC IFTHEN=(WHEN=GROUP,BEGIN=(4,1,CH,EQ,C'A'), END=(6,1,CH,EQ,C'B'),PUSH=(9:1,2))

When 'A' and 'B' both are present you can pick that data with another IFTHEN condition.

I am sure after reading this you'll start writing an application program ;-)

and that is why I was waiting for an expert opinion from Bill

.
Rohit Umarjikar

Global Moderator

Joined: 21 Sep 2010
Posts: 2572
Location: NYC,USA

 Posted: Tue Apr 21, 2015 4:31 am I think, this can be done with DFSORT with the experts help as seen. But the point is why to make things more complex than already it is unless there is no COBOL in your shop? SO, it is easy to write a COBOL program for such complex requirements and also easy to maintain. A new person would go crazy if he is not a DFSORT expert Also , a SAS card could be easy to achieve this in ase you are running out of choice.
Bill Woodger

Moderator Emeritus

Joined: 09 Mar 2011
Posts: 7311
Location: Inside the Matrix

Posted: Tue Apr 21, 2015 5:16 am

Claes,

Here's some code.

 Code: //DANSTRNO EXEC PGM=SORT //SYSOUT   DD SYSOUT=* //SORTOUT  DD SYSOUT=* //SYSIN    DD *                                                       JOINKEYS F1=INA,FIELDS=(OUTJ-ODDEVEN-SEQ,A)   JOINKEYS F2=INB,FIELDS=(OUTJ-ODDEVEN-SEQ,A)                                                       JOIN UNPAIRED,F1                                                       REFORMAT FIELDS=(F1:OUTJ-RECORD,F2:OUTJ-RECORD)                                                       SORT FIELDS=(REF-F1-STREET,                 A,                REF-F1-ODDEVEN,                 D,                REF-F1-NUMBER,                 A),                EQUALS                                                       OUTREC IFTHEN=(WHEN=INIT,                    OVERLAY=(REFEX-NUMBER-DIFF:                             REF-F2-NUMBER,                              SUB,                             REF-F1-NUMBER,                              TO=ZD,                               LENGTH=5)),                                                              IFTHEN=(WHEN=GROUP,                    KEYBEGIN=(REF-F1-STREET-ODDEVEN),                    PUSH=(REFEX-START-NUMBER:                           REF-F1-NUMBER,                           REF-F1-NUMBER)),                                                              IFTHEN=(WHEN=GROUP,                    BEGIN=(REFEX-NUMBER-DIFF,                            NE,                           DIFFERENCE-OF-TWO,                          AND,                           REF-F1-STREET-ODDEVEN,                            EQ,                           REF-F2-STREET-ODDEVEN),                    END=(REF-F1-STREET-ODDEVEN,                        NE,                         REF-F2-STREET-ODDEVEN),                    PUSH=(REFEX-SAVE-F2-NUMBER:                           REF-F2-NUMBER)),                                                                      IFTHEN=(WHEN=(REF-F1-STREET-ODDEVEN,                         NE,                        REF-F2-STREET-ODDEVEN),                    OVERLAY=(REFEX-START-NUMBER:                              REFEX-SAVE-F2-NUMBER))                                                               OUTFIL INCLUDE=(REF-F1-STREET-ODDEVEN,                    NE,                   REF-F2-STREET-ODDEVEN,                  OR,                   REFEX-NUMBER-DIFF,                    NE,                   DIFFERENCE-OF-TWO),                                                                      BUILD=(REF-F1-STREET,                 10X,                 REFEX-START-NUMBER,                 10X,                 REF-F1-NUMBER,                 10X,                 4X,                 REF-F1-ODDEVEN,                  CHANGE=(1,                           C'0',                            C'E',                           C'1',                            C'O')),                                                                      HEADER1=('STREET     HOUSEFROM  HOUSETO EVEN/ODD')                                                             //JNF1CNTL DD *                                                         INREC IFTHEN=(WHEN=INIT,                  OVERLAY=(EXTJ-LAST-OF-NUMBER-BITS:                            INJ-HOUSE-NO-LAST,                            TRAN=BIT)),                                                               IFTHEN=(WHEN=(EXTJ-LAST-BIT-OF-BITS,                      EQ,                       EVEN-BIT-VALUE),                   BUILD=(INJ-STREET-REF,                          EXTJ-LAST-BIT-OF-BITS,                          INJ-HOUSE-NO,                          SEQNUM,7,ZD,START=5000000)),                                                               IFTHEN=(WHEN=NONE,                   BUILD=(INJ-STREET-REF,                          EXTJ-LAST-BIT-OF-BITS,                          INJ-HOUSE-NO,                          SEQNUM,7,ZD,START=1))                                                       //JNF2CNTL DD *                                                         INREC IFTHEN=(WHEN=INIT,                  OVERLAY=(EXTJ-LAST-OF-NUMBER-BITS:                            INJ-HOUSE-NO-LAST,                            TRAN=BIT)),                                                               IFTHEN=(WHEN=(EXTJ-LAST-BIT-OF-BITS,                      EQ,                       EVEN-BIT-VALUE),                   BUILD=(INJ-STREET-REF,                          EXTJ-LAST-BIT-OF-BITS,                          INJ-HOUSE-NO,                          SEQNUM,7,ZD,START=4999999)),                                                               IFTHEN=(WHEN=NONE,                   BUILD=(INJ-STREET-REF,                          EXTJ-LAST-BIT-OF-BITS,                          INJ-HOUSE-NO,                          SEQNUM,7,ZD,START=0))                                               //SYMNAMES DD * * INPUT RECORD TO JNFNCNTL  INJ-STREET-REF,1,1,CH  SKIP,12  INJ-HOUSE-NO,*,1,ZD  POSITION,INJ-HOUSE-NO  INJ-HOUSE-NO-LAST,=,1,ZD * EXTENSION FROM JNFNCNTL  EXTJ-LAST-OF-NUMBER-BITS,*,8,CH  POSITION,EXTJ-LAST-OF-NUMBER-BITS  SKIP,7  EXTJ-LAST-BIT-OF-BITS,*,1,CH * OUTPUT FROM JNFNCNTL  OUTJ-RECORD,1,10,CH  OUTJ-STREET-REF,1,1,CH  OUTJ-ODDEVEN-BIT,*,1,CH  OUTJ-NUMBER,*,1,CH  OUTJ-ODDEVEN-SEQ,*,7,CH * REFORMAT RECORD  REF-F1-STREET,1,1,CH  REF-F1-ODDEVEN,*,1,CH  POSITION,REF-F1-STREET  REF-F1-STREET-ODDEVEN,=,2,CH  REF-F1-NUMBER,*,1,ZD  REF-F1-SEQUENCE,*,7,CH  REF-F2-STREET,*,1,CH  REF-F2-ODDEVEN,*,1,CH  POSITION,REF-F2-STREET  REF-F2-STREET-ODDEVEN,=,2,CH  REF-F2-NUMBER,*,1,ZD  REF-F2-SEQUENCE,*,7,CH * EXTEND REFORMAT RECORD  REFEX-NUMBER-DIFF,*,5,CH  REFEX-START-NUMBER,*,1,CH  REFEX-SAVE-F2-NUMBER,*,1,CH * VALUES  EVEN-BIT-VALUE,C'0'  ODD-BIT-VALUE,C'1'  DIFFERENCE-OF-TWO,C'00002' //SYMNOUT DD SYSOUT=*

Requires //INA and //INB for the data, same dataset on both DDs. I used this data:

 Code: 1            1 1            2 1            3 1            4 1            5 1            6 1            7 2            1 2            3 2            7 3            3 3            5 3            7 3            9 4            1 4            2 4            3 4            4 4            5 4            6 4            7 4            8 5            1 5            2 5            4 5            5 5            7 5            8

Streets 4 and 5 are your two samples.

Output is:

 Code: STREET     HOUSEFROM  HOUSETO EVEN/ODD 1          1          7              O 1          2          6              E 2          1          3              O 2          7          7              O 3          3          9              O 4          1          7              O 4          2          8              E 5          1          1              O 5          5          7              O 5          2          4              E 5          8          8              E

Now, as we agreed, I don't think this is particularly complex. I know you like symbols/SYMNAMES, and in the solution they are effective for a number of reasons, not only that you can just change the input format and not have to change anything else, but they have a documentary value, and save repetition of digit-code, which is prone to typos (typo a symbol and you know about it).

The main part of the solution uses the JOINKEYS-same-dsn-offset-sequence trick. This is a simple way to get data from consecutive records "side by side". Once you have the data side-by-side, that's a large part of the game.

Because odd and even numbers must be treated separately, two sequence numbers are applied in the JNFnCNTL files, for F1 starting from 1 and 5000000 (if there are more than 5m streets in Denmark, you'll have to increase this value); for F2, starting from zero and 4999999.

Since we've mentioned two ways already to tell odd/even, here's a third, which is similar to the bit-test, but just to show TRAN=BIT in operation. The last digit of the house number is converted to a character representation of the bit-pattern, and the last of those bits tells whether even (0) or odd (1).

Having a mix of sequence numbers from two different series, the data must be sorted for each of the JOINKEYS, which will match on the sequence number.

The JOIN is UNPAIRED,F1 because the 0 and 4999999 records will not match and are not needed. The two final F1 of each sequence are also unmatched, but they are needed.

The REFORMAT statement is only presented with the required data, so that is straightforward. All the odds are together on one sequence range, and all the evens on another.

The REFORMAT statement can be amended to exclude the sequence numbers, but they are left for now for investigative purposes.

The data is then SORTed to bring the odds and evens back together.

OUTREC is then used to: subtract the adjacent house numbers (highest from lowest); make a GROUP with KEYBEGIN on the street ad odd/even marker; make a group for "distance" between numbers not being two to get a new start-number for that group, the important thing here is to have the END for this group, as this type of group may not occur within a stree, and we don't want a previous value working through; WHEN=(logical-expression) to use the correct (new) start-number on key-change.

OUTFIL selects all the end-of-key records, and formats them a little.

You'll need some work to change the symbols/SYMNAMES for the input to match your data (lengths and position of streets and numbers, avoid (or keep) any other data) but hopefully it can all be done there without having to change the actual code. If that can't be done, let me know and we should be able to fix it.

Output format is also yours to play with. I just made a simple example.

Lightly tested.

The other solution I was thinking of would not require any SORTs, but there would be more code wrapped around the Main Task code above. I may code that up if I have a moment.

RahulG31,

If we pass the data lots of times, although we can get a solution, it will be more effective to do it in a general-purpose language instead. The trick in the solution has many applications, so experiment and understand.

Rohit,

Since it can be maintained just by changing the symbols/SYMNAMES, I'd hope even novices could manage it fairly well.

The code may also look long (when you need to look at it), but that is to make it easier to read and understand. Here it is after DFSORT has processed the symbols:

SYSIN:

 Code: JOINKEYS F1=INA,FIELDS=(4,7,A)                                          JOINKEYS F2=INB,FIELDS=(4,7,A)                                           JOIN UNPAIRED,F1                                                      REFORMAT FIELDS=(F1:1,10,F2:1,10)                                      SORT FIELDS=(1,1,CH,A,2,1,CH,D,3,1,ZD,A),EQUALS                        OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(21:13,1,ZD,SUB,3,1,ZD,TO=ZD,LENGTH=5*               )),IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,2),PUSH=(26:3,1,3,1)),*               IFTHEN=(WHEN=GROUP,BEGIN=(21,5,CH,NE,C'00002',AND,1,2,CH*               ,EQ,11,2,CH),END=(1,2,CH,NE,11,2,CH),PUSH=(27:13,1)),IFT*               HEN=(WHEN=(1,2,CH,NE,11,2,CH),OVERLAY=(26:27,1))    OUTFIL INCLUDE=(1,2,CH,NE,11,2,CH,OR,21,5,CH,NE,C'00002'),BUILD=(1,1,1*               0X,26,1,10X,3,1,10X,4X,2,1,CHANGE=(1,C'0',C'E',C'1',C'O'*               )),HEADER1=('STREET     HOUSEFROM  HOUSETO EVEN/ODD')

JNF1CNTL:

 Code: INREC IFTHEN=(WHEN=INIT,OVERLAY=(15:14,1,TRAN=BIT)),IFTHEN=(WHEN=(22,1*               ,CH,EQ,C'0'),BUILD=(1,1,22,1,14,1,SEQNUM,7,ZD,START=5000*               000)),IFTHEN=(WHEN=NONE,BUILD=(1,1,22,1,14,1,SEQNUM,7,ZD*               ,START=1))

JNF2CNTL:

 Code: INREC IFTHEN=(WHEN=INIT,OVERLAY=(15:14,1,TRAN=BIT)),IFTHEN=(WHEN=(22,1*               ,CH,EQ,C'0'),BUILD=(1,1,22,1,14,1,SEQNUM,7,ZD,START=4999*               999)),IFTHEN=(WHEN=NONE,BUILD=(1,1,22,1,14,1,SEQNUM,7,ZD*               ,START=0))
Claes Norreen

Active User

Joined: 20 Dec 2005
Posts: 137
Location: Denmark

 Posted: Tue Apr 21, 2015 12:07 pm Wauw, this is brilliant, Bill! Thanks a lot! I will try to grasp what is going on now.. Also want to thank you, RahulG31. After your 2nd post, I understood what you were trying to do - which wasn't that far from what Bill came up with.
Claes Norreen

Active User

Joined: 20 Dec 2005
Posts: 137
Location: Denmark

Posted: Tue Apr 21, 2015 12:23 pm

Oh, I tried this:
 Code: 6            1 6            5 6            9

Got this:
 Code: 6          1          1              O 6          1          5              O 6          9          9              O

But expected this:
 Code: 6          1          1              O 6          5          5              O 6          9          9              O

Is it fixable, Bill?
Bill Woodger

Moderator Emeritus

Joined: 09 Mar 2011
Posts: 7311
Location: Inside the Matrix

 Posted: Tue Apr 21, 2015 12:33 pm Thanks Claes, I suspect it can be fixed :-) I probably can't look for about an hour, but I'll update it here.
Claes Norreen

Active User

Joined: 20 Dec 2005
Posts: 137
Location: Denmark

Posted: Tue Apr 21, 2015 2:59 pm

Thanks, Bill

I've detected the flaw to be somewhere here:
 Code: IFTHEN=(WHEN=GROUP,                                                     BEGIN=(REFEX-NUMBER-DIFF,NE,DIFFERENCE-OF-TWO,AND,                        REF-F1-STREET-ODDEVEN,EQ,REF-F2-STREET-ODDEVEN),               END=(REF-F1-STREET-ODDEVEN,NE,REF-F2-STREET-ODDEVEN),              PUSH=(REFEX-SAVE-F2-NUMBER:REF-F2-NUMBER)),                   IFTHEN=(WHEN=(REF-F1-STREET-ODDEVEN,NE,                                           REF-F2-STREET-ODDEVEN),                                 OVERLAY=(REFEX-START-NUMBER:REFEX-SAVE-F2-NUMBER))

Bill Woodger

Moderator Emeritus

Joined: 09 Mar 2011
Posts: 7311
Location: Inside the Matrix

 Posted: Tue Apr 21, 2015 5:12 pm Yes, thanks. That is the place. The problem is with consecutive "breaks" in the range within a street. Causes the GROUP to have acted on the previous record and then the GROUP acts again before the data from the previous record can be used. A sequence number on the GROUP then store in different locations depending on the sequence number being odd/even would do it. On it now :-)
RahulG31

Active User

Joined: 20 Dec 2014
Posts: 446
Location: USA

 Posted: Tue Apr 21, 2015 6:19 pm Thanks for your thoughts Bill. I really never thought that this was achievable in a single step. :-)
Bill Woodger

Moderator Emeritus

Joined: 09 Mar 2011
Posts: 7311
Location: Inside the Matrix

Posted: Tue Apr 21, 2015 6:34 pm

Claes,

A couple of changes, so easiest to provide the entire OUTREC:

 Code: OUTREC IFTHEN=(WHEN=INIT,                    OVERLAY=(REFEX-NUMBER-DIFF:                             REF-F2-NUMBER,                              SUB,                             REF-F1-NUMBER,                              TO=ZD,                               LENGTH=5,                             REFEX-SEQ:                              SEQNUM,1,ZD)),                                                              IFTHEN=(WHEN=GROUP,                    KEYBEGIN=(REF-F1-STREET-ODDEVEN),                    PUSH=(REFEX-START-NUMBER:                           REF-F1-NUMBER,                           REF-F1-NUMBER)),                                                              IFTHEN=(WHEN=GROUP,                    BEGIN=(REFEX-NUMBER-DIFF,                            NE,                           DIFFERENCE-OF-TWO,                          AND,                           REF-F1-STREET-ODDEVEN,                            EQ,                           REF-F2-STREET-ODDEVEN),                    END=(REF-F1-STREET-ODDEVEN,                        NE,                         REF-F2-STREET-ODDEVEN),                    PUSH=(REFEX-SAVE-F2-NUMBER:                           REF-F2-NUMBER)),                                                             IFTHEN=(WHEN=GROUP,                       BEGIN=(REFEX-SEQ,                             EQ,                              AN-ODD-NUMBER),                       RECORDS=2,                       PUSH=(REFEX-LEFT-DIFF:                              REFEX-NUMBER-DIFF)),                                                             IFTHEN=(WHEN=GROUP,                       BEGIN=(REFEX-SEQ,                             EQ,                              AN-EVEN-NUMBER),                       RECORDS=2,                       PUSH=(REFEX-RIGHT-DIFF:                              REFEX-NUMBER-DIFF)),                                                             IFTHEN=(WHEN=(REF-F1-STREET-ODDEVEN,                        NE,                       REF-F2-STREET-ODDEVEN),                    OVERLAY=(REFEX-START-NUMBER:                              REFEX-SAVE-F2-NUMBER)),                                                             IFTHEN=(WHEN=(REFEX-RIGHT-DIFF,                        NE,                       DIFFERENCE-OF-TWO,                      AND,                        REFEX-LEFT-DIFF,                         NE,                       DIFFERENCE-OF-TWO),                   OVERLAY=(REFEX-START-NUMBER:                            REF-F1-NUMBER))

Then these after their fellow-named items:

 Code: REFEX-SEQ,*,1,BI  REFEX-LEFT-DIFF,*,5,CH  REFEX-RIGHT-DIFF,*,5,CH

A simpler sequence number per record, one digit long, is set. The difference is PUSHed to one or other location, depending on odd/even sequence number.

If neither of those left/right differences are two, then the start number of a sequence of one numbers is the current number.

Output with your new data is:

 Code: STREET     HOUSEFROM  HOUSETO EVEN/ODD 1          1          7              O 1          2          6              E 2          1          3              O 2          7          7              O 3          3          9              O 4          1          7              O 4          2          8              E 5          1          1              O 5          5          7              O 5          2          4              E 5          8          8              E 6          1          1              O 6          5          5              O 6          9          9              O

Edit: We need some DFSORT code to support various indentation preferences :-)
Bill Woodger

Moderator Emeritus

Joined: 09 Mar 2011
Posts: 7311
Location: Inside the Matrix

 Posted: Tue Apr 21, 2015 6:42 pm RahulG31, That's why I'm recommending you master it and add it to your store of SORT tools :-)
Bill Woodger

Moderator Emeritus

Joined: 09 Mar 2011
Posts: 7311
Location: Inside the Matrix

Posted: Tue Apr 21, 2015 6:45 pm

Forgot to mention this little trick. If your OUTFIL is not presenting all the data from the input to it (because it is redundant) as is common with this type of solution, include something like this:

 Code: OUTFIL FNAMES=TMPD

It allows you to see the undisturbed input to the actual OUTFIL which is doing the work.

Needs a DD in the JCL of course, which can just be SYSOUT=...
Claes Norreen

Active User

Joined: 20 Dec 2005
Posts: 137
Location: Denmark

 Posted: Tue Apr 21, 2015 6:50 pm Thanks, Bill. It looks very promising! I'll look at it in depths tomorrow, as I've already been here much too long today...
chandan.inst

Active User

Joined: 03 Nov 2005
Posts: 275
Location: Mumbai

 Posted: Wed Apr 22, 2015 8:55 am Awesome Bill.. As per RahulG31 I also never thought its doable with single step. But yes we have DFSORT masters like you
Claes Norreen

Active User

Joined: 20 Dec 2005
Posts: 137
Location: Denmark

Posted: Wed Apr 22, 2015 12:06 pm

Bill Woodger wrote:
Forgot to mention this little trick. If your OUTFIL is not presenting all the data from the input to it (because it is redundant) as is common with this type of solution, include something like this:

 Code: OUTFIL FNAMES=TMPD

It allows you to see the undisturbed input to the actual OUTFIL which is doing the work.

Needs a DD in the JCL of course, which can just be SYSOUT=...

Convert the step to ICETOOL (I generally never use SORT anymore, as with ICETOOL it's easier to add more input statements, thus easier to maintain). Then I did this to see the input to JOINKEYS:
 Code: COPY FROM(INA) TO(OUT) USING(JNF1) and COPY FROM(INB) TO(OUT) USING(JNF2)  and then commented out the (new) * SORT JKFROM TO(OUT) USING(CTL1)
Claes Norreen

Active User

Joined: 20 Dec 2005
Posts: 137
Location: Denmark

Posted: Wed Apr 22, 2015 4:59 pm

Hi Bill,

I've now done a real test against the Danish streets. To do this, I first made the Streetcode 8,ZD, and the Housenumber 3,ZD - using your brilliant symbol file, that was a walk in the park..

Now, there's an issue with my collegues street, Anyvej (we do call it Anyway ).. There are some strange holes on this street. Here's the input:
 Code: 00008548    002 00008548    004 00008548    006 00008548    008 00008548    009 00008548    010 00008548    011 00008548    012 00008548    013 00008548    014 00008548    016 00008548    020 00008548    022 00008548    024 00008548    026 00008548    028

And output:
 Code: STREETCODE     HOUSE-FR  HOUSE-TO  EVEN/ODD 00008548         009       013         O    00008548         016       016         E    00008548         020       028         E

Somehow, it misses out on the first even interval from 2-16, and only registers 16? Maybe this has to do with your recent change?

BR, Claes
Bill Woodger

Moderator Emeritus

Joined: 09 Mar 2011
Posts: 7311
Location: Inside the Matrix

Posted: Wed Apr 22, 2015 6:13 pm

Hi Claes,

I made that street number 7, and used only two-digit house numbers and got this:
 Code: 7          09          13              O 7          02          16              E 7          20          28              E

Whilst attempting this I did make a mistake by not increasing the size of OUTJ-RECORD and things got confusing. Once I changed 10 to 11 it was OK.

The other tricky part of the change is to get the last digit of the house number for odd/even. For three digits it would be:

 Code: INJ-HOUSE-NO,*,3,ZD   POSITION,INJ-HOUSE-NO   SKIP,2   INJ-HOUSE-NO-LAST,*,1,ZD

The curious thing is that I'd expect there to be more items listed (if something is out with the definitions) rather than have something go missing.

 Code: STREET            HOUSEFROM    HOUSETO   EVEN/ODD 00008548          009          013              O 00008548          002          016              E 00008548          020          028              E

This is the complete symbol file I used for that output:

 Code: * INPUT RECORD TO JNFNCNTL  INJ-STREET-REF,1,8,CH  SKIP,4  INJ-HOUSE-NO,*,3,ZD  POSITION,INJ-HOUSE-NO  SKIP,2  INJ-HOUSE-NO-LAST,*,1,ZD * EXTENSION FROM JNFNCNTL  EXTJ-LAST-OF-NUMBER-BITS,*,8,CH  POSITION,EXTJ-LAST-OF-NUMBER-BITS  SKIP,7  EXTJ-LAST-BIT-OF-BITS,*,1,CH * OUTPUT FROM JNFNCNTL  OUTJ-RECORD,1,19,CH  OUTJ-STREET-REF,1,8,CH  OUTJ-ODDEVEN-BIT,*,1,CH  OUTJ-NUMBER,*,3,CH  OUTJ-ODDEVEN-SEQ,*,7,CH * REFORMAT RECORD  REF-F1-STREET,1,8,CH  REF-F1-ODDEVEN,*,1,CH  POSITION,REF-F1-STREET  REF-F1-STREET-ODDEVEN,=,9,CH  REF-F1-NUMBER,*,3,ZD  REF-F1-SEQUENCE,*,7,CH  REF-F2-STREET,*,8,CH  REF-F2-ODDEVEN,*,1,CH  POSITION,REF-F2-STREET  REF-F2-STREET-ODDEVEN,=,9,CH  REF-F2-NUMBER,*,3,ZD  REF-F2-SEQUENCE,*,7,CH * EXTEND REFORMAT RECORD  REFEX-NUMBER-DIFF,*,5,CH  REFEX-START-NUMBER,*,3,CH  REFEX-SAVE-F2-NUMBER,*,3,CH  REFEX-SEQ,*,1,BI  REFEX-LEFT-DIFF,*,5,CH

I'll try some more testing as well, I think.

I'll also see if I can improve on the symbol definitions, there are several places needing lengths to be changed: it would be nice if there were just one :-)

If your symbols match the above, can you post the previous street to the one where you see the problem, please?
Claes Norreen

Active User

Joined: 20 Dec 2005
Posts: 137
Location: Denmark

Posted: Wed Apr 22, 2015 7:20 pm

Hi Bill,

I've got the exact same symbol file - except that I've defined
 Code: INJ-LRECL,*
right after
 Code: EXTJ-LAST-BIT-OF-BITS
and also (on page 2 that you did not present):
 Code: REFEX-RIGHT-DIFF,*,5,CH      REFEX-LRECL,*              * VALUES                        EVEN-BIT-VALUE,C'0'          ODD-BIT-VALUE,C'1'            DIFFERENCE-OF-TWO,C'00002'

 Code: REFEX-LRECL,*
field.

The LRECL fields I use in INREC IFOUTLEN=lrecl - just to be able to indent like I wish to...
 Code: OUTREC IFOUTLEN=REFEX-LRECL,                            IFTHEN=(WHEN=INIT,                                      OVERLAY=(REFEX-NUMBER-DIFF:                              REF-F2-NUMBER,SUB,REF-F1-NUMBER,TO=ZD,LENGTH=5,       REFEX-SEQ:SEQNUM,1,ZD)),
I'm positive this won't affect output, right?

I've isolated the test to just Anyvej - meaning I use this input:
 Code: 00008548    002 00008548    004 00008548    006 00008548    008 00008548    009 00008548    010 00008548    011 00008548    012 00008548    013 00008548    014 00008548    016 00008548    020 00008548    022 00008548    024 00008548    026 00008548    028
For this input, I get this output:
 Code: STREETCODE     HOUSE-FR  HOUSE-TO  EVEN/ODD 00008548         009       013         O    00008548         016       016         E    00008548         020       028         E
Can you please verify you get the correct output using my input file here - then I must have copied something wrong somewhere..

BR
/Claes
Bill Woodger

Moderator Emeritus

Joined: 09 Mar 2011
Posts: 7311
Location: Inside the Matrix

Posted: Wed Apr 22, 2015 7:38 pm

That data was the same as I had. I ran it anyway, and it produced the same results as I showed previously:

 Code: STREET            HOUSEFROM    HOUSETO   EVEN/ODD 00008548          009          013              O 00008548          002          016              E 00008548          020          028              E

That "lrecl" is a nice thing. I can' do that, as I don't have DFSORT 2.1, which greatly extended the places where a symbol could be used. It adds one unnecessary byte, but a small price to pay in this type of situation.

I can't imagine how the IFOUTLEN would affect the results. I'll try a manual one anyway.
Bill Woodger

Moderator Emeritus

Joined: 09 Mar 2011
Posts: 7311
Location: Inside the Matrix

Posted: Wed Apr 22, 2015 7:49 pm

Using IFOUTLEN=61 didn't affect the results.

I'm wondering if something went wrong when you re-indented?

Here's the cards I'm running after the symbol conversion:

 Code: JOINKEYS F1=INA,FIELDS=(13,7,A)                                        JOINKEYS F2=INB,FIELDS=(13,7,A)                                         JOIN UNPAIRED,F1                                                      REFORMAT FIELDS=(F1:1,19,F2:1,19)                                      SORT FIELDS=(1,8,CH,A,9,1,CH,D,10,3,ZD,A),EQUALS                        OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(39:29,3,ZD,SUB,10,3,ZD,TO=ZD,LENGTH=*               5,50:SEQNUM,1,ZD)),IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,9),PUS*               H=(44:10,3,10,3)),IFTHEN=(WHEN=GROUP,BEGIN=(39,5,CH,NE,C*               '00002',AND,1,9,CH,EQ,20,9,CH),END=(1,9,CH,NE,20,9,CH),P*               USH=(47:29,3)),IFTHEN=(WHEN=GROUP,BEGIN=(50,1,BI,EQ,B'..*               .....1'),RECORDS=2,PUSH=(51:39,5)),IFTHEN=(WHEN=GROUP,BE*               GIN=(50,1,BI,EQ,B'.......0'),RECORDS=2,PUSH=(56:39,5)),I*               FTHEN=(WHEN=(1,9,CH,NE,20,9,CH),OVERLAY=(44:47,3)),IFTHE*               N=(WHEN=(56,5,CH,NE,C'00002',AND,51,5,CH,NE,C'00002'),OV*               ERLAY=(44:10,3)),IFOUTLEN=61                              OUTFIL INCLUDE=(1,9,CH,NE,20,9,CH,OR,39,5,CH,NE,C'00002'),BUILD=(1,8,1*               0X,44,3,10X,10,3,10X,4X,9,1,CHANGE=(1,C'0',C'E',C'1',C'O*               ')),HEADER1=('STREET            HOUSEFROM    HOUSETO   E*               VEN/ODD')                                                OUTFIL FNAMES=TMPD

And:

 Code: INREC IFTHEN=(WHEN=INIT,OVERLAY=(16:15,1,TRAN=BIT)),IFTHEN=(WHEN=(23,1*                ,CH,EQ,C'0'),BUILD=(1,8,23,1,13,3,SEQNUM,7,ZD,START=5000*                000)),IFTHEN=(WHEN=NONE,BUILD=(1,8,23,1,13,3,SEQNUM,7,ZD*                ,START=1))

And:

 Code: INREC IFTHEN=(WHEN=INIT,OVERLAY=(16:15,1,TRAN=BIT)),IFTHEN=(WHEN=(23,1*               ,CH,EQ,C'0'),BUILD=(1,8,23,1,13,3,SEQNUM,7,ZD,START=4999*               999)),IFTHEN=(WHEN=NONE,BUILD=(1,8,23,1,13,3,SEQNUM,7,ZD*               ,START=0))
 View Bookmarks All times are GMT + 6 Hours
 Goto page 1, 2  Next

 Topic Forum Replies Similar Topics Join lines with dfsort DFSORT/ICETOOL 5 Covert k to 1000 / m to 1000000 using... DFSORT/ICETOOL 5 DFSORT Output file order query DFSORT/ICETOOL 2 DFSORT-How to convert ZD to ZS in DFSORT DFSORT/ICETOOL 2 DFSORT to handle in between specific ... DFSORT/ICETOOL 6
Search our Forums: