Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Count number of record in input and flag them

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: Count number of record in input and flag them
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: 80
Location: Australia

PostPosted: Fri Feb 24, 2012 5:36 pm    Post subject: Reply to: Count number of record in input and flag them
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: 80
Location: Australia

PostPosted: Fri Feb 24, 2012 5:39 pm    Post subject:
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    Post subject:
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    Post subject: Reply to: Count number of record in input and flag them
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    Post subject: Reply to: Count number of record in input and flag them
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: 578
Location: USA

PostPosted: Fri Feb 24, 2012 8:57 pm    Post subject:
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7241

PostPosted: Sat Feb 25, 2012 7:04 am    Post subject: Reply to: Count number of record in input and flag them
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: 1703
Location: Australia

PostPosted: Sat Feb 25, 2012 2:12 pm    Post subject:
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    Post subject: Reply to: Count number of record in input and flag them
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: 1703
Location: Australia

PostPosted: Mon Feb 27, 2012 12:27 pm    Post subject:
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    Post subject:
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    Post subject:
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: 578
Location: USA

PostPosted: Mon Feb 27, 2012 8:19 pm    Post subject:
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: 1703
Location: Australia

PostPosted: Tue Feb 28, 2012 2:38 am    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Performing arithmetic on input field zh_lad DFSORT/ICETOOL 24 Tue Dec 06, 2016 8:04 pm
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm
This topic is locked: you cannot edit posts or make replies. How to use 2 input files in control c... Gunapala CN DFSORT/ICETOOL 23 Thu Oct 13, 2016 3:42 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us