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

Count number of record in input and flag them
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
thunderstorm

New User


Joined: 23 Mar 2007
Posts: 35
Location: pune
PostPosted: Fri Feb 24, 2012 5:19 pm
 Reply with quote
Hi,

My need here is to flag each record 'Y' or 'N' based on number of records present for that key in the file.

For eg, my input file, contains Bank code and states where it has a branch. There will be multiple records for same bank code if present in multiple states.

If the number of occurence of the Bank code in the file is more than three (more than three states) then flag each record for that bank as 'Y'. Rest all banks to be flaged as 'N' which has less than three occurence/state.

Record format:

Bank Code: PIC X(5)
Filler: PIC X(01)
State: PIC X(2)
Length: 80

Input Data:

Code:
X1234 TX
Y7868 CA
X1234 CA
X1234 MN
E3456 CA
Y7868 NY
E3456 AZ
E3456 NJ
G9999 TN
X1234 AZ
E3456 NY
Y7868 AZ
G9999 NY


Output Data:

Record format:

Bank Code: PIC X(5)
Filler: PIC X(01)
State: PIC X(2)
Filler: PIC X(01)
Flag: PIC X(01)
Length: 80

Code:
X1234 TX Y   
Y7868 CA N   
X1234 CA Y   
X1234 MN Y   
E3456 CA Y   
Y7868 NY N   
E3456 AZ Y   
E3456 NJ Y   
G9999 TN N   
X1234 AZ Y   
E3456 NY Y   
Y7868 AZ N

Key here is Bank code. Incase it has to be in sorted order, then it is not a problem as well.There is no particular sort order needed in the output file. Only requirement is to tag the records as 'Y' or 'N'.

I have attached an image of mainfram snap shot if the above posting of file data is not clear.

Please let me know in case the requirement is not clear.


Thanks again for all your help.

note from a moderator
post edited to use the code tags
and attachment deleted
Back to top
View user's profile Send private message
charanmsrit

New User


Joined: 25 Oct 2007
Posts: 81
Location: Australia
PostPosted: Fri Feb 24, 2012 5:36 pm
 Reply with quote
Do you want your output to be in the same order as input?? or is it ok if it's sorted on bank code?
Back to top
View user's profile Send private message
charanmsrit

New User


Joined: 25 Oct 2007
Posts: 81
Location: Australia
PostPosted: Fri Feb 24, 2012 5:39 pm
 Reply with quote
sorry, ignore my previous message. i missed your info a bit does you shop use DFSORT or SYNCSORT?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
PostPosted: Fri Feb 24, 2012 5:45 pm
 Reply with quote
charanmsrit wrote:
sorry, ignore my previous message. i missed your info a bit does you shop use DFSORT or SYNCSORT?


based on previous topics started by the TS, he uses DFSORT.
Back to top
View user's profile Send private message
Nikhil Jain

New User


Joined: 20 Jul 2011
Posts: 16
Location: India
PostPosted: Fri Feb 24, 2012 6:30 pm
 Reply with quote
Hi,

Please find below the ICETOOL Cards which you can try using.

Code:
//SRT1CNTL DD *                                           
  SORT FIELDS=(1,5,CH,A)                                   
//SRT2CNTL DD *                                           
  SORT FIELDS=COPY                                         
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,5),PUSH=(11:SEQ=1))
//SRT3CNTL DD *                                           
  SORT FIELDS=COPY                                         
  INCLUDE COND=(11,1,ZD,EQ,3)                             
  OUTREC OVERLAY=(13:C'Y')                                 
//SRT4CNTL DD *                                           
  SORT FIELDS=COPY                                         
  JOINKEYS F1=OUTPUT,FIELDS=(1,5,A)                       
  JOINKEYS F2=OUTPUT2,FIELDS=(1,5,A)                       
  JOIN UNPAIRED F1                                         
  REFORMAT FIELDS=(F1:1,8,F2:13,1)                         
//TOOLIN DD *                                   
  COPY FROM(INPUT) TO(OUTPUT) USING(SRT1)       
  COPY FROM(OUTPUT) TO(OUTPUT1) USING(SRT2)     
  COPY FROM(OUTPUT1) TO(OUTPUT2) USING(SRT3)   
  COPY FROM(OUTPUT2) TO(OUTPUT3) USING(SRT4)   


I hope a blank field can be considered as 'N'.

This might not be the most efficient way of solving this issue both performance wise & complexity wise but, this can definitely give you a start.

Looking forward to see replies from the SORT Wizards.
Back to top
View user's profile Send private message
Nikhil Jain

New User


Joined: 20 Jul 2011
Posts: 16
Location: India
PostPosted: Fri Feb 24, 2012 6:50 pm
 Reply with quote
Just clicked, you can try playing around with OCCUR also.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Fri Feb 24, 2012 8:57 pm
 Reply with quote
thunderstorm,

See if below helps...
Code:
//STEP0100 EXEC PGM=SORT                                               
//SYSOUT   DD SYSOUT=*                                                 
//INA      DD *                                                       
X1234 TX                                                               
Y7868 CA                                                               
X1234 CA                                                               
X1234 MN                                                               
E3456 CA                                                               
Y7868 NY                                                               
E3456 AZ                                                               
E3456 NJ                                                               
G9999 TN                                                               
X1234 AZ                                                               
E3456 NY                                                               
Y7868 AZ                                                               
G9999 NY                                                               
//INB      DD *                                                       
X1234 TX                                                               
Y7868 CA                                                               
X1234 CA                                                               
X1234 MN                                                               
E3456 CA                                                               
Y7868 NY                                                               
E3456 AZ                                                               
E3456 NJ                                                               
G9999 TN                                                               
X1234 AZ                                                               
E3456 NY                                                               
Y7868 AZ                                                               
G9999 NY                                                               
//SORTOUT  DD SYSOUT=*                                                 
//SYSIN    DD *                                                       
  OPTION COPY                                                         
  JOINKEYS F1=INA,FIELDS=(01,5,A)                                     
  JOINKEYS F2=INB,FIELDS=(01,5,A)                                     
  REFORMAT FIELDS=(F1:1,80,F2:6,8)                                   
  INREC IFOUTLEN=80,IFTHEN=(WHEN=INIT,OVERLAY=(10:C'N')),             
                    IFTHEN=(WHEN=(81,8,ZD,GT,3),OVERLAY=(10:C'Y'))   
//*                                                                   
//JNF2CNTL DD *                                                       
  INREC BUILD=(1,5,C'00000001')                                       
  SUM FIELDS=(6,8,ZD)                                                 
//*                                                                   


OUTPUT
Code:
E3456 CA Y
E3456 AZ Y
E3456 NJ Y
E3456 NY Y
G9999 TN N
G9999 NY N
X1234 TX Y
X1234 CA Y
X1234 MN Y
X1234 AZ Y
Y7868 CA N
Y7868 NY N
Y7868 AZ N

Thanks,
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
PostPosted: Sat Feb 25, 2012 7:04 am
 Reply with quote
A two-step alternative. Data only sorted once.

If the data were already sorted, the sequence/include could go in the JNF2CNTL for one step.

The control cards are those following the resolution of symnames, plus a little edit for the final IFTHEN which got split at line end.

Code:
//MULTBNKA EXEC PGM=SORT           
//SYSOUT   DD SYSOUT=*
//SORTOUT  DD DSN=&&INA,SPACE=(TRK,1),UNIT=SYSDA,DISP=(NEW,PASS)
//MULTOUT  DD DSN=&&INB,SPACE=(TRK,1),UNIT=SYSDA,DISP=(NEW,PASS)
//SYSIN    DD *
 SORT FIELDS=(1,5,CH,A)                                         
 OUTREC IFTHEN=(WHEN=INIT,OVERLAY(81:SEQNUM,5,ZD,RESTART=(1,5)))
 OUTFIL FNAMES=MULTOUT,INCLUDE=(81,5,ZD,EQ,4),BUILD=(1,5)       
 OUTFIL BUILD=(1,80)                                             
//*
//SORTIN   DD *
X1234 TX
Y7868 CA
X1234 CA
X1234 MN
E3456 CA
Y7868 NY
E3456 AZ
E3456 NJ
G9999 TN
X1234 AZ
E3456 NY
Y7868 AZ
G9999 NY
//MULTBNKB EXEC PGM=SORT
//INA DD DSN=&&INA,DISP=(OLD,PASS)
//INB DD DSN=&&INB,DISP=(OLD,PASS)
//SYSOUT   DD SYSOUT=*
//SORTOUT  DD SYSOUT=*
//SYSIN    DD *
  OPTION COPY                                                           
 JOINKEYS F1=INA,FIELDS=(1,5,A),SORTED,NOSEQCK                         
 JOINKEYS F2=INB,FIELDS=(1,5,A),SORTED,NOSEQCK                         
  JOIN UNPAIRED,F1                                                     
 REFORMAT FIELDS=(F1:1,80,?)                                           
 INREC IFOUTLEN=80,IFTHEN=(WHEN=(81,1,CH,EQ,C'B'),OVERLAY=(10:C'Y')),
                      IFTHEN=(WHEN=NONE,OVERLAY=(10:C'N'))                       


Code:
E3456 AZ Y
E3456 NY Y
E3456 NJ Y
E3456 CA Y
G9999 NY N
G9999 TN N
X1234 TX Y
X1234 CA Y
X1234 AZ Y
X1234 MN Y
Y7868 CA N
Y7868 AZ N
Y7868 NY N
Back to top
View user's profile Send private message
gcicchet

Senior Member


Joined: 28 Jul 2006
Posts: 1702
Location: Australia
PostPosted: Sat Feb 25, 2012 2:12 pm
 Reply with quote
Hi,

here is another way
Code:
//S1       EXEC  PGM=ICETOOL                                     
//TOOLMSG  DD SYSOUT=*                                           
//DFSMSG   DD SYSOUT=*                                           
//IN       DD *                                                   
X1234 TX                                                         
Y7868 CA                                                         
X1234 CA                                                         
X1234 MN                                                         
E3456 CA                                                         
Y7868 NY                                                         
E3456 AZ                                                         
E3456 NJ                                                         
G9999 TN                                                         
X1234 AZ                                                         
E3456 NY                                                         
Y7868 AZ                                                         
G9999 NY                                                         
/*                                                               
//LT4      DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS) 
//OUT      DD SYSOUT=*                                           
//TOOLIN   DD *                                                       
SELECT FROM(IN) TO(OUT) ON(1,5,CH) HIGHER(3) DISCARD(LT4) USING(CTL1)
COPY FROM(LT4) TO(OUT) USING(CTL2)                                   
/*                                                                   
//CTL1CNTL DD *                                                       
  INREC OVERLAY=(10:C'Y')                                             
/*                                                                   
//CTL2CNTL DD *                                                       
  INREC OVERLAY=(10:C'N')                                             
/*                                                                   


Gerry
Back to top
View user's profile Send private message
thunderstorm

New User


Joined: 23 Mar 2007
Posts: 35
Location: pune
PostPosted: Mon Feb 27, 2012 10:35 am
 Reply with quote
Wow... I am overwhelmed by the responses... I am trying one solution after the other... As of now I tried gcicchets' response.

I guess I had to add one more line to the sort step.

COPY FROM(LT4) TO(OUT1) USING(CTL1)

And then combine both the control card.I tried and it worked.
I am trying other options as well. I will keep you posted on the other results.

Thanks to all wonderful people....
Back to top
View user's profile Send private message
gcicchet

Senior Member


Joined: 28 Jul 2006
Posts: 1702
Location: Australia
PostPosted: Mon Feb 27, 2012 12:27 pm
 Reply with quote
Hi,

I tried to follow what you changed but I'm struggling, can you please show the code and JCL you used.


Gerry
Back to top
View user's profile Send private message
thunderstorm

New User


Joined: 23 Mar 2007
Posts: 35
Location: pune
PostPosted: Mon Feb 27, 2012 3:18 pm
 Reply with quote
Hi Gerry,

Your sort card only gives me output which contains records with 'N' and ignores records with 'Y'. So I modified your code as follows.

Code:
//S1       EXEC  PGM=ICETOOL                                     
//TOOLMSG  DD SYSOUT=*                                           
//DFSMSG   DD SYSOUT=*                                           
//IN       DD *                                                   
X1234 TX                                                         
Y7868 CA                                                         
X1234 CA                                                         
X1234 MN                                                         
E3456 CA                                                         
Y7868 NY                                                         
E3456 AZ                                                         
E3456 NJ                                                         
G9999 TN                                                         
X1234 AZ                                                         
E3456 NY                                                         
Y7868 AZ                                                         
G9999 NY                                                         
/*                                                               
//LT4      DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS) 
//OUT      DD DSN=DS1     
//OUT1    DD DSN=DS2                             
//TOOLIN   DD *                                                       
SELECT FROM(IN) TO(OUT) ON(1,5,CH) HIGHER(3) DISCARD(LT4) USING(CTL1)
COPY FROM(LT4) TO(OUT) USING(CTL2) 
COPY FROM(LT4) TO(OUT1) USING(CTL1)                                 
/*                                                                   
//CTL1CNTL DD *                                                       
  INREC OVERLAY=(10:C'Y')                                             
/*                                                                   
//CTL2CNTL DD *                                                       
  INREC OVERLAY=(10:C'N')           


Added below two lines

Code:

//OUT1    DD DSN=DS2 
COPY FROM(LT4) TO(OUT1) USING(CTL1)

Hope I am not wrong icon_exclaim.gif
Back to top
View user's profile Send private message
Nikhil Jain

New User


Joined: 20 Jul 2011
Posts: 16
Location: India
PostPosted: Mon Feb 27, 2012 7:07 pm
 Reply with quote
thunderstorm wrote:
Added below two lines

Code:

//OUT1    DD DSN=DS2 
COPY FROM(LT4) TO(OUT1) USING(CTL1)

Hope I am not wrong icon_exclaim.gif


Hi,

I would suggest you recheck your input & rerun the JCL. The code which Gerry has posted worked as expected for me.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Mon Feb 27, 2012 8:19 pm
 Reply with quote
thunderstorm,
Do you not have updated PTF instaleld or did you not try joinkey solution?

Thanks,
Back to top
View user's profile Send private message
gcicchet

Senior Member


Joined: 28 Jul 2006
Posts: 1702
Location: Australia
PostPosted: Tue Feb 28, 2012 2:38 am
 Reply with quote
Hi Thunderstorm,

if you are writing the output to a dataset you need a DISP=(MOD,CATLG,DELETE) and you will find the code I provided will work.



Gerry
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 TRIM everything from input, output co... DFSORT/ICETOOL 1
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts To get the count of rows for every 1 ... DB2 3
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us