Posted: Fri May 02, 2003 7:46 am Post subject: How to group records based on a change in particular field
Hi,
I want to get a summary report based on a change in name (which is between columns 79 and 111) in a 133 byte report. example, the name starts at position 79
A.E.E DE PUERTORICO
AEE DE PUERTO RICO
BANCO POPULAR DE P.R.
BANCO POPULAR DE PUERTO RICO
MAIL TRIBUNE
MAIL TRIBUNE
MAIL TRIBUNE
MAIL TRIBUNE
MAIL TRIBUNE
SANTA CRUZ SENTINEL
SANTA CRUZ COUNTY SENTINEL
SANTA CRUZ COUNTY SENTINEL
I want to group the records by change in name and have a sub-total for each group. a single record for each group would be fine. Thanks
! We use Syncsort !
A.E.E DE PUERTORICO 1
AEE DE PUERTO RICO 1
BANCO POPULAR DE P.R. 1
BANCO POPULAR DE PUERTO RICO 1
MAIL TRIBUNE 5
SANTA CRUZ COUNTY SENTINEL 2
SANTA CRUZ SENTINEL 1
Output is :
NAME COUNT
-------------------------------- ---------------
A.E.E DE PUERTORICO 1
AEE DE PUERTO RICO 1
BANCO POPULAR DE P.R. 1
BANCO POPULAR DE PUERTO RICO 1
MAIL TRIBUNE 5
SANTA CRUZ COUNTY SENTINEL 2
SANTA CRUZ SENTINEL 1
Method3:
Code:
//STEP3 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=YOUR INPUT FILE,
// DISP=SHR
//SORTOUT DD SYSOUT=*
//SYSIN DD *
INREC FIELDS=(1,133,X'000000001C') * INCLUDE COUNTER
SORT FIELDS=(79,32,CH,A) * SORT ON CONTROL FIELD
SUM FIELDS=(134,5,PD) * SUMMARIZE IN COUNTER
OUTFIL OUTREC=(79,32,X,134,5,PD,EDIT=(IIIIIIIIT))
/*
Joined: 26 Nov 2002 Posts: 12375 Topics: 75 Location: San Jose
Posted: Sun May 04, 2003 8:26 pm Post subject:
Suresh,
you need another step to read in the output created above and sort on the count descending.The following Jcl will give you the desired results.
Code:
//STEP1 EXEC PGM=SYNCTOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN DD DSN=USERID.GROUP.FILE,
// DISP=SHR
//T1 DD DSN=&T1,DISP=(,PASS),SPACE=(CYL,(10,10),RLSE)
//OUT DD SYSOUT=*
//TOOLIN DD *
SORT FROM(IN) TO(T1) USING(CTL1)
SORT FROM(T1) TO(OUT) USING(CTL2)
//CTL1CNTL DD *
INREC FIELDS=(1,133,X'000000001C') * INCLUDE COUNTER
SORT FIELDS=(79,32,CH,A) * SORT ON CONTROL FIELD
SUM FIELDS=(134,5,PD) * SUMMARIZE IN COUNTER
OUTREC FIELDS=(79,32,X,134,5,PD,EDIT=(IIIIIIIT))
//CTL2CNTL DD *
SORT FIELDS=(34,8,CH,D) * SORT ON COUNT DESC
/*
The output of this job is :
Code:
MAIL TRIBUNE 5
SANTA CRUZ COUNTY SENTINEL 2
A.E.E DE PUERTORICO 1
AEE DE PUERTO RICO 1
BANCO POPULAR DE P.R. 1
BANCO POPULAR DE PUERTO RICO 1
SANTA CRUZ SENTINEL 1
Joined: 26 Nov 2002 Posts: 12375 Topics: 75 Location: San Jose
Posted: Thu Oct 16, 2008 12:41 pm Post subject:
mf_user,
Quote:
I was trying to modify the below solution given by you to use SEQNUM but getting non-sense output
what makes it a non-sense output? you used ZD on the outrec and a default edit mask is used and removed the leading zeros. If you just want the leading zeros, just remove from ZD from outrec _________________ Kolusu
www.linkedin.com/in/kolusu
Joined: 26 Nov 2002 Posts: 12375 Topics: 75 Location: San Jose
Posted: Fri Oct 17, 2008 10:39 am Post subject:
mf_user,
Quote:
My problem is why I am not getting the output like below with SEQNUM used. Is it possible?
Do you even know what seqnum does ? Seqnum is basically putting a relative record number on all records. The sequence numbers are assigned in the order in which the records are received. The first post is counting the duplicates for each key and I have no idea as to how you arrived that you can solve the same issue using the seqnum. _________________ Kolusu
www.linkedin.com/in/kolusu
Kolusu, thanks for clearning my doubts regarding SEQNUM. _________________ MF
==
Any training that does not include the emotions, mind and body is incomplete; knowledge fades without feeling.
==
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
