IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Inserting records in a file on a value from input


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
ssa

New User


Joined: 03 Apr 2022
Posts: 10
Location: United States

PostPosted: Sun Apr 03, 2022 1:40 am
Reply with quote

Hi Friends!
I am new to the forum and need whatever help I can get to find a solution to a problem I am facing.

I have a file which I created using SORT as a comma delimited csv for reporting.
Input File looks like below, first I have a header and then the detailed records corresponding to it follows

Code:
ABCD:
ABCD,XYX1234,XYX   ,002,YES
ABCD,XYX1226,XYX   ,002,YES
ABCD,XYX1226,XYX   ,001,NO
ABCD,BB1241 ,BB    ,001,YES
,
EFGH:
EFGH,SA1242 ,SA    ,001,YES
EFGH,XV01   ,XV    ,001,YES
EFGH,X234V1 ,XV    ,001,NO

I want to right the summary of the above records right after the detailed one for each header. Point to note here is last 3 columns in the record act as a key based on which I have to write the summary records (Write only Unique). example as below:

Code:
ABCD:
ABCD,XYX1234,XYX   ,002,YES
ABCD,XYX1226,XYX   ,002,YES
ABCD,XYX1226,XYX   ,001,NO
ABCD,BB1241 ,BB    ,001,YES
           YES     NO     TOTAL
XYX        2        1         3
BB         1        0         1
,
EFGH:
EFGH,SA1242 ,SA    ,001,YES
EFGH,XV01   ,XV    ,001,YES
EFGH,X234V1 ,XV    ,001,NO
           YES     NO     TOTAL
SA         1        0         1
XV         1        1         2




I used ICETOOL to write the summary records in a separate output file, but I need to write both detailed and summary records in same file.

Request to help/guide me please.

Appreciate in advance.
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2011
Location: USA

PostPosted: Sun Apr 03, 2022 3:20 am
Reply with quote

What did you try so far?

Where are the samples of YOUR OWN code?
Back to top
View user's profile Send private message
ssa

New User


Joined: 03 Apr 2022
Posts: 10
Location: United States

PostPosted: Sun Apr 03, 2022 9:00 pm
Reply with quote

this is what I had tried


Code:
//STEP1     EXEC PGM=ICETOOL
//IN        DD DSN=FILE1,DISP=SHR
//OUT       DD DSN=FILE2,DISP=(,CATLG,DELETE)
//TOOLIN   DD *
  SPLICE FROM(IN) TO(OUT) ON(1,6) WITHANY KEEPNODUPS -
             WITH(1,6) WITH(8,3) WITH(13,3)  USING(CTL1)
//CTL1CNTL DD *
  INREC IFTHEN=(WHEN=(25,3,CH,EQ,C'YES'),
             BUILD=(1:14,6,8:21,3)),
            IFTHEN=(WHEN=(25,3,CH,EQ,C'NO'),
             BUILD=(1:14,6,13:21,3))
/*
//TOOLMSG  DD SYSOUT=*
//DFSMSG   DD SYSOUT=*


just to add a note here, till now I am not writing the header with (Yes/No and its Total with value). Once I have the records written to same file as detailed, I should be able to address that.

Kindly let me know if any further details are needed.
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2011
Location: USA

PostPosted: Mon Apr 04, 2022 1:06 am
Reply with quote

AFAIU the number of possible keys like XYX is not fixed within the same key like ABCD?
If so, you may need two-three stages of ICETOOL, or two-three job steps of DFSORT.

First, create an intermediate sums table (or "file") like this one:
Code:
ABCD,       ,XYX   ,   ,YES    2
ABCD,       ,XYX   ,   ,NO     1
ABCD,       ,XYX   ,   ,       3
ABCD,       ,BB    ,   ,YES    1
ABCD,       ,BB    ,   ,       1
EFGH,       ,SA    ,   ,YES    1
EFGH,       ,SA    ,   ,       1
EFGH,       ,XV    ,   ,YES    1
EFGH,       ,XV    ,   ,NO     1
EFGH,       ,XV    ,   ,       2


Next, merge this intermediate table with your original dataset, to produce the report you need.

I personally would recommend not to use combined ICETOOL operations like SPLICE, but instead use basic DFSORT operations. This approach would allow you to better understand how the process really works, and why.
Back to top
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1231
Location: Bamberg, Germany

PostPosted: Mon Apr 04, 2022 2:38 am
Reply with quote

I am able to get the following output with two DFSORT steps.
Code:
ABCD:                           
ABCD,XYX1234,XYX   ,002,YES     
ABCD,XYX1226,XYX   ,002,YES     
ABCD,XYX1226,XYX   ,001,NO     
ABCD,BB1241 ,BB    ,001,YES     
       YES  NO TOTAL           
BB       1   0     1           
XYX      4   1     5           
EFGH:                           
EFGH,SA1242 ,SA    ,001,YES     
EFGH,XV01   ,XV    ,001,YES     
EFGH,X234V1 ,XV    ,001,NO     
       YES  NO TOTAL           
SA       1   0     1           
XV       1   1     2           
Back to top
View user's profile Send private message
ssa

New User


Joined: 03 Apr 2022
Posts: 10
Location: United States

PostPosted: Mon Apr 04, 2022 2:39 am
Reply with quote

sergeyken wrote:
AFAIU the number of possible keys like XYX is not fixed within the same key like ABCD?


Hi Sergeyken,
Appreciate your timely response

yes that's correct, possible keys for ABCD or others are not fixed. Some might have only 1, while some might have hundreds.

My initial report had only 3 columns - first, second and fifth i.e. ABCD,XYX1234,YES

then later was asked to add a column which will reflect the alphabets in 2nd column and how many times we have those Alphabets under Yes or No, 4th column denotes that like XYX with YES happens 2 times, and with NO only once, which I was able to do. now the latest additional requirement is to print the counters as a summary below the key ABCD.

I will try to generate the table with totals, but what I didn't understand is how we will be able to write only 1 record for XYX or BB etc horizontally. I am not much aware of how merge works, will do some reading and test it.

if you can guide me that will really help, from my end I will try it too.

Thank You once again!
Back to top
View user's profile Send private message
ssa

New User


Joined: 03 Apr 2022
Posts: 10
Location: United States

PostPosted: Mon Apr 04, 2022 2:42 am
Reply with quote

Joerg.Findeisen wrote:
I am able to get the following output with two DFSORT steps.
Code:
ABCD:                           
ABCD,XYX1234,XYX   ,002,YES     
ABCD,XYX1226,XYX   ,002,YES     
ABCD,XYX1226,XYX   ,001,NO     
ABCD,BB1241 ,BB    ,001,YES     
       YES  NO TOTAL           
BB       1   0     1           
XYX      4   1     5           
EFGH:                           
EFGH,SA1242 ,SA    ,001,YES     
EFGH,XV01   ,XV    ,001,YES     
EFGH,X234V1 ,XV    ,001,NO     
       YES  NO TOTAL           
SA       1   0     1           
XV       1   1     2           



Thanks Joerg. This is exactly what I need my output to look like with just one correction the counter for XYX under YES should be same as in the column which is 2.
Could you please guide me how you got it.
Back to top
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1231
Location: Bamberg, Germany

PostPosted: Mon Apr 04, 2022 12:14 pm
Reply with quote

This is what I have used to achieve the requirement. There may be better solutions than mine after tweaking the code.

Code:
//WHATEVER EXEC PGM=ICEMAN                                     
//SORTIN   DD *                                               
ABCD:                                                         
ABCD,XYX1234,XYX   ,002,YES                                   
ABCD,XYX1226,XYX   ,002,YES                                   
ABCD,XYX1226,XYX   ,001,NO                                     
ABCD,BB1241 ,BB    ,001,YES                                   
EFGH:                                                         
EFGH,SA1242 ,SA    ,001,YES                                   
EFGH,XV01   ,XV    ,001,YES                                   
EFGH,X234V1 ,XV    ,001,NO                                     
/*                                                             
//SYSOUT   DD SYSOUT=*                                         
//SORTOUT  DD DISP=(NEW,PASS),UNIT=SYSALLDA,SPACE=(CYL,(2,1)) 
//SYSIN    DD *                                               
  OMIT COND=(5,1,CH,EQ,C':')                                   
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,4),PUSH=(35:ID=3)),     
    IFTHEN=(WHEN=INIT,OVERLAY=(38:SEQNUM,2,BI,RESTART=(1,4))),
    IFTHEN=(WHEN=(25,1,CH,EQ,C'Y'),                           
      OVERLAY=(46:+1,BI,LENGTH=2,2Z,46,2)),                   
    IFTHEN=(WHEN=NONE,                                         
      OVERLAY=(46:2Z,+1,BI,LENGTH=2,48,2))                     
  SORT FIELDS=(1,4,CH,A,14,6,CH,A)                             
  OUTFIL FNAMES=(SORTOUT),                                     
    REMOVECC,                                                 
    IFTHEN=(WHEN=(38,2,BI,EQ,+1),                             
      BUILD=(1,4,C':',35:35,3,2Z,/,1,34,35,5)),               
    SECTIONS=(1,4,14,6,                                       
      TRAILER3=(7X,C'YES',2X,C'NO',X,C'TOTAL',35:35,3,/,       
                14,6,X,                                       
                TOT=(46,2,BI,M10,LENGTH=3),X,                 
                TOT=(48,2,BI,M10,LENGTH=3),X,                 
                TOT=(50,2,BI,M10,LENGTH=5),35:35,3,14,6))     
  END                                                         
/*                                                             
//FINALIZE EXEC PGM=ICEMAN                           
//SORTIN   DD DISP=(OLD,PASS),DSN=*.WHATEVER.SORTOUT 
//SYSOUT   DD SYSOUT=*                               
//SORTOUT  DD SYSOUT=*                               
//SYSIN    DD *                                       
  OPTION EQUALS                                       
  SORT FIELDS=(35,11,BI,A)                           
  SUM FIELDS=(NONE)                                   
  OUTREC BUILD=(1,34)                                 
  END                                                 
/*

Output:
Code:
****** ****************************
000001 ABCD:                       
000002 ABCD,XYX1234,XYX   ,002,YES 
000003 ABCD,XYX1226,XYX   ,002,YES 
000004 ABCD,XYX1226,XYX   ,001,NO   
000005 ABCD,BB1241 ,BB    ,001,YES 
000006        YES  NO TOTAL         
000007 BB       1   0     1         
000008 XYX      2   1     3         
000009 EFGH:                       
000010 EFGH,SA1242 ,SA    ,001,YES 
000011 EFGH,XV01   ,XV    ,001,YES 
000012 EFGH,X234V1 ,XV    ,001,NO   
000013        YES  NO TOTAL         
000014 SA       1   0     1         
000015 XV       1   1     2         
****** ****************************
Back to top
View user's profile Send private message
ssa

New User


Joined: 03 Apr 2022
Posts: 10
Location: United States

PostPosted: Mon Apr 04, 2022 2:15 pm
Reply with quote

Thanks Joerg. much appreciated.
I will try this out, will definitely have to tweak the code, since I have to add some more data (additional records) to the report in the summary. Hope it will work.

Will keep posted.

Thank You!
Back to top
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1231
Location: Bamberg, Germany

PostPosted: Mon Apr 04, 2022 2:53 pm
Reply with quote

The better the sample data, the better the output. Other members of the forum, as said, may have better solutions.
I am confident the addition of more fields will work, you need to imagine what fields are needed before coding to get the result you aim for.
Back to top
View user's profile Send private message
ssa

New User


Joined: 03 Apr 2022
Posts: 10
Location: United States

PostPosted: Tue Apr 05, 2022 12:50 am
Reply with quote

Thanks Joerg for the help.

My latest input data has changed slightly and now it has another summary detail as shown below:

Code:
ABCD:
ABCD,XYX1234,XYX   ,002,YES
ABCD,XYX1226,XYX   ,002,YES
ABCD,XYX1226,XYX   ,001,NO
ABCD,BB1241 ,BB    ,001,YES
ABCD,1234567,      ,   ,YES
,
TOTAL YES Keys: 004      TOTAL NO: 001
SUM OF ALL YES/NO: 005
,
EFGH:
EFGH,SA1242 ,SA    ,001,YES
EFGH,XV01   ,XV    ,001,YES
EFGH,X234V1 ,XV    ,001,NO
EFGH,9876543,      ,   ,NO
,
TOTAL YES Keys: 002      TOTAL NO: 002
SUM OF ALL YES/NO: 004


the sort card provided by you worked well until I had those 2 summary records printed in input. I even placed an OVERLAY criteria if TOTAL or SUM found in 1st position then, it should not be part of Sections, but it keeps on writing additonal line above and below Total and Sum at 1st position.

like this as shown below:

Code:
ABCD:                           
ABCD,XYX1234,XYX   ,002,YES     
ABCD,XYX1226,XYX   ,002,YES     
ABCD,XYX1226,XYX   ,001,NO     
ABCD,BB1241 ,BB    ,001,YES     
       YES  NO TOTAL           
BB       1   0     1           
XYX      2   1     3   
TOTA
TOTAL YES Keys: 004      TOTAL NO: 001
       YES  NO TOTAL
: 00       
SUM OF ALL YES/NO: 005
       YES  NO TOTAL
 /NO
Back to top
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1231
Location: Bamberg, Germany

PostPosted: Tue Apr 05, 2022 12:56 am
Reply with quote

Make it easy on me. Show me the new Input and the desired Output.

PS: OMIT the two new lines as well as they don't seem to be needed.
Back to top
View user's profile Send private message
ssa

New User


Joined: 03 Apr 2022
Posts: 10
Location: United States

PostPosted: Tue Apr 05, 2022 2:36 am
Reply with quote

Hi Joerg.
New input is as below, addition to the old one is that for the keys like ABCD, EFGH, etc we could have only numeric values in 2nd column (position 6 of 7 bytes ), which will result in blanks for columns 3 and 4. But can still hold value YES/NO.

addition of 2 lines shows the summary of the detailed records, how many Total Yes/NO present including the records having space in column 3, so we have to keep those 2 lines for each key.

Hence input will be as below:

Code:
ABCD:
ABCD,XYX1234,XYX   ,002,YES
ABCD,XYX1226,XYX   ,002,YES
ABCD,XYX1226,XYX   ,001,NO
ABCD,BB1241 ,BB    ,001,YES
ABCD,1234567,      ,   ,YES
,
TOTAL YES Keys: 004      TOTAL NO: 001
SUM OF ALL YES/NO: 005
,
EFGH:
EFGH,SA1242 ,SA    ,001,YES
EFGH,XV01   ,XV    ,001,YES
EFGH,X234V1 ,XV    ,001,NO
EFGH,9876543,      ,   ,NO
,
TOTAL YES Keys: 002      TOTAL NO: 002
SUM OF ALL YES/NO: 004



while the expected output should look like as below:

Code:
ABCD:
ABCD,XYX1234,XYX   ,002,YES
ABCD,XYX1226,XYX   ,002,YES
ABCD,XYX1226,XYX   ,001,NO
ABCD,BB1241 ,BB    ,001,YES
ABCD,1234567,      ,   ,YES
,
       YES  NO TOTAL           
BB       1   0     1           
XYX      2   1     3 
,
TOTAL YES Keys: 004      TOTAL NO: 001
SUM OF ALL YES/NO: 005
,
EFGH:
EFGH,SA1242 ,SA    ,001,YES
EFGH,XV01   ,XV    ,001,YES
EFGH,X234V1 ,XV    ,001,NO
EFGH,9876543,      ,   ,NO
,
       YES  NO TOTAL           
SA       1   0     1           
XV       1   1     2
,
TOTAL YES Keys: 002      TOTAL NO: 002
SUM OF ALL YES/NO: 004


The alphabets in 3rdcolumn derived from 2nd column is important as those shows different types of terminals (for specific business purpose) which is required for reporting.
Back to top
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1231
Location: Bamberg, Germany

PostPosted: Tue Apr 05, 2022 9:32 am
Reply with quote

Code:
//WHATEVER EXEC PGM=ICEMAN                                     
//SORTIN   DD *                                               
ABCD:                                                         
ABCD,XYX1234,XYX   ,002,YES                                   
ABCD,XYX1226,XYX   ,002,YES                                   
ABCD,XYX1226,XYX   ,001,NO                                     
ABCD,BB1241 ,BB    ,001,YES                                   
ABCD,1234567,      ,   ,YES                                   
,                                                             
TOTAL YES Keys: 004      TOTAL NO: 001                         
SUM OF ALL YES/NO: 005                                         
,                                                             
EFGH:                                                         
EFGH,SA1242 ,SA    ,001,YES                                   
EFGH,XV01   ,XV    ,001,YES                                   
EFGH,X234V1 ,XV    ,001,NO                                     
EFGH,9876543,      ,   ,NO                                     
,                                                             
TOTAL YES Keys: 002      TOTAL NO: 002                         
SUM OF ALL YES/NO: 004                                         
/*                                                             
//SYSOUT   DD SYSOUT=*                                         
//SORTOUT  DD DISP=(NEW,PASS),UNIT=SYSALLDA,SPACE=(CYL,(2,1)) 
//DATA     DD DISP=(NEW,PASS),UNIT=SYSALLDA,SPACE=(CYL,(2,1)) 
//SYSIN    DD *                                               
  INREC IFTHEN=(WHEN=GROUP,                                           
    BEGIN=(5,1,CH,EQ,C':'),PUSH=(45:ID=5,SEQ=5)),                     
    IFTHEN=(WHEN=(25,1,CH,EQ,C'Y'),OVERLAY=(55:+1,BI,LENGTH=2,2Z)),   
    IFTHEN=(WHEN=(25,1,CH,EQ,C'N'),OVERLAY=(55:2Z,+1,BI,LENGTH=2)),   
    IFTHEN=(WHEN=(1,1,CH,EQ,C',',OR,1,6,SS,EQ,C'TOTAL ,SUM OF'),       
      OVERLAY=(50:SEQNUM,5,ZD,START=99900,INCR=10,RESTART=(45,5)))     
  SORT FIELDS=(1,4,CH,A,14,6,CH,A)                                     
  OUTFIL FNAMES=(SORTOUT),                                             
    INCLUDE=(25,1,SS,EQ,C'YN',AND,14,1,CH,NE,C' '),                   
    REMOVECC,                                                         
    IFTHEN=(WHEN=INIT,OVERLAY=(50:SEQNUM,5,ZD,START=2,RESTART=(1,4))),
    SECTIONS=(1,4,14,6,                                               
      TRAILER3=(14,6,X,                                               
                TOT=(55,2,BI,M10,LENGTH=3),X,                         
                TOT=(57,2,BI,M10,LENGTH=3),X,                         
                COUNT=(M10,LENGTH=5),45:45,5,C'999001'))               
  OUTFIL FNAMES=(DATA),SAVE,                                           
    REMOVECC,                                                         
    IFTHEN=(WHEN=(50,5,ZD,EQ,+99900),                                 
      BUILD=(C',',45:45,10,/,                                         
             C'       YES  NO TOTAL',45:45,5,+999000,ZD,LENGTH=6,/,   
             1,49,+999002,ZD,LENGTH=6))                               
  END                                                                 
/*                                                                     
//FINALIZE EXEC PGM=ICEMAN                                             
//SORTIN   DD DISP=(OLD,PASS),DSN=*.WHATEVER.SORTOUT                   
//         DD DISP=(OLD,PASS),DSN=*.WHATEVER.DATA                     
//SYSOUT   DD SYSOUT=*                                                 
//SORTOUT  DD SYSOUT=*                                                 
//SYSIN    DD *                                                       
  SORT FIELDS=(45,11,BI,A)                                             
  OUTREC BUILD=(1,44)                                                 
  END                                                                 
/*

Output:
Code:
****** **************************** Datenanfang ****
000001 ABCD:                                       
000002 ABCD,BB1241 ,BB    ,001,YES                 
000003 ABCD,XYX1234,XYX   ,002,YES                 
000004 ABCD,XYX1226,XYX   ,002,YES                 
000005 ABCD,XYX1226,XYX   ,001,NO                   
000006 ABCD,1234567,      ,   ,YES                 
000007 ,                                           
000008        YES  NO TOTAL                         
000009 BB       1   0     1                         
000010 XYX      2   1     3                         
000011 ,                                           
000012 TOTAL YES Keys: 004      TOTAL NO: 001       
000013 SUM OF ALL YES/NO: 005                       
000014 ,                                           
000015 EFGH:                                       
000016 EFGH,SA1242 ,SA    ,001,YES                 
000017 EFGH,XV01   ,XV    ,001,YES                 
000018 EFGH,X234V1 ,XV    ,001,NO                   
000019 EFGH,9876543,      ,   ,NO                   
000020 ,                                           
000021        YES  NO TOTAL                         
000022 SA       1   0     1                         
000023 XV       1   1     2                         
000024 ,                                           
000025 TOTAL YES Keys: 002      TOTAL NO: 002       
000026 SUM OF ALL YES/NO: 004                       
****** **************************** Datenende ******
Back to top
View user's profile Send private message
ssa

New User


Joined: 03 Apr 2022
Posts: 10
Location: United States

PostPosted: Tue Apr 05, 2022 1:15 pm
Reply with quote

Thank You Joerg, much appreciated it.
I will try this and let you know.
Back to top
View user's profile Send private message
ssa

New User


Joined: 03 Apr 2022
Posts: 10
Location: United States

PostPosted: Wed Apr 06, 2022 1:33 am
Reply with quote

Hi Joerg,
It worked perfectly, thank you so very much.
I need to learn/practice more about sections, very useful.
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2011
Location: USA

PostPosted: Thu Apr 07, 2022 10:30 pm
Reply with quote

Instead of
Code:
1,6,SS,EQ,C'TOTAL ,SUM OF'

might be better (for clarity, and efficiency) to code it as
Code:
1,6,CH,EQ,L(C'TOTAL',C'SUM OF')


Using format ZD instead of BI for counters would make the debugging of code easier. (Though, sorting of intermediate results would also need appropriate updates)
Back to top
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1231
Location: Bamberg, Germany

PostPosted: Thu Apr 07, 2022 11:48 pm
Reply with quote

AFAIK the L(..) Syntax is not supported by DFSORT, but I may be wrong. Regarding the ZD format, this would indeed be better for debugging the code. icon_exclaim.gif
Back to top
View user's profile Send private message
ssa

New User


Joined: 03 Apr 2022
Posts: 10
Location: United States

PostPosted: Sat Apr 09, 2022 2:28 am
Reply with quote

I will definitely try that just to know different ways of achieving the successful result.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts How to split large record length file... DFSORT/ICETOOL 7
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts Access to non cataloged VSAM file JCL & VSAM 18
No new posts Compare only first records of the fil... SYNCSORT 7
Search our Forums:

Back to Top