|
View previous topic :: View next topic
|
| Author |
Message |
thunderstorm
New User
.jpg)
Joined: 23 Mar 2007
Posts: 35
Location: pune
|
|
|
|
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 |
|
 |
charanmsrit
New User
Joined: 25 Oct 2007
Posts: 81
Location: Australia
|
|
|
|
| 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 |
|
|
charanmsrit
New User
Joined: 25 Oct 2007
Posts: 81
Location: Australia
|
|
|
|
| sorry, ignore my previous message. i missed your info a bit does you shop use DFSORT or SYNCSORT? |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6965
Location: porcelain throne
|
|
|
|
| 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 |
|
|
Nikhil Jain
New User
Joined: 20 Jul 2011
Posts: 16
Location: India
|
|
|
|
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 |
|
|
Nikhil Jain
New User
Joined: 20 Jul 2011
Posts: 16
Location: India
|
|
|
|
| Just clicked, you can try playing around with OCCUR also. |
|
| Back to top |
|
|
sqlcode1
Active Member
Joined: 08 Apr 2010
Posts: 577
Location: USA
|
|
|
|
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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
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 |
|
|
gcicchet
Senior Member
Joined: 28 Jul 2006
Posts: 1702
Location: Australia
|
|
|
|
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 |
|
|
thunderstorm
New User
Joined: 23 Mar 2007
Posts: 35
Location: pune
|
|
|
|
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 |
|
|
gcicchet
Senior Member
Joined: 28 Jul 2006
Posts: 1702
Location: Australia
|
|
|
|
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 |
|
|
thunderstorm
New User
Joined: 23 Mar 2007
Posts: 35
Location: pune
|
|
|
|
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  |
|
| Back to top |
|
|
Nikhil Jain
New User
Joined: 20 Jul 2011
Posts: 16
Location: India
|
|
|
|
| thunderstorm wrote: |
Added below two lines
| Code: |
//OUT1 DD DSN=DS2
COPY FROM(LT4) TO(OUT1) USING(CTL1) |
Hope I am not wrong |
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 |
|
|
sqlcode1
Active Member
Joined: 08 Apr 2010
Posts: 577
Location: USA
|
|
|
|
thunderstorm,
Do you not have updated PTF instaleld or did you not try joinkey solution?
Thanks, |
|
| Back to top |
|
|
gcicchet
Senior Member
Joined: 28 Jul 2006
Posts: 1702
Location: Australia
|
|
|
|
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 |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
