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
 

 

How to get unique count inside dfsort sections.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
Kiruthi R

New User


Joined: 21 Sep 2011
Posts: 4
Location: India

PostPosted: Wed Sep 21, 2011 11:17 am    Post subject: How to get unique count inside dfsort sections.
Reply with quote

Hi,
I have one master file.I need to generate a report ,sort key is name ,city,branch. I have to report total net amount,unique total account numbers,UNIQUE cusips count for each group.
SAMPLE REPORT:
Code:
SECTION 1
NAME1 CITY1 BRANCH1 ACCTNUM1 CUSIP1  10,000
NAME1 CITY1 BRANCH1 ACCTNUM2 CUSIP1   5,000
NAME1 CITY1 BRANCH1 ACCTNUM2 CUSIP2   5,000
 TOTAL AMOUNT 20,000
  ACCT COUNT 2
  CUSIP COUNT 2
SECTION 2
NAME2 CITY2 BRANCH2 ACCTNUM3 CUSIP3  10,000
NAME2 CITY2 BRANCH2 ACCTNUM3 CUSIP3   5,000
NAME2 CITY2 BRANCH2 ACCTNUM4 CUSIP4   5,000
 TOTAL AMOUNT 20,000
  ACCT COUNT 2
  CUSIP COUNT 2

Im able to get all the details except UNIQUE count for acct and cusip in each group. UNIQUE keyword display the count for full report.Is there any way to get unique counts within section.Please advise.
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7315

PostPosted: Wed Sep 21, 2011 2:31 pm    Post subject: Reply to: How to get unique count inside dfsort sections.
Reply with quote

Please show what you have so far.
Back to top
View user's profile Send private message
Kiruthi R

New User


Joined: 21 Sep 2011
Posts: 4
Location: India

PostPosted: Wed Sep 21, 2011 3:36 pm    Post subject:
Reply with quote

Hi,
Please see this..

Code:

  SORT FIELDS=(1,10,CH,A,11,6,CH,A,17,6,CH,A)
  OUTFIL FNAMES=REPORT,
   SECTIONS=(1,22,SKIP=2L,
    HEADER3=(3:X,/,
             3:'NAME',16:'CITY',25:'BRANCH',33:'ACCT NUM',48:'CUSIPS',57:'AMOUNT',/,
             3:'----',16:'----',25:'------',33:'------'48:'------',57:'------'),
    TRAILER3=(
              3:'Total Amount =',
              18:TOT=(52,8,BI,EDIT=(IIIIIIIIT.TT)),/,
               3:'ACCT COUNT =',
             18:COUNT=(?),/,
              3:'CUSIP COUNT =',
             18:COUNT=(?))),
   OUTREC=(3:1,10,
                  17:11,6,
                  26:17,6,
                  34:23,12,
                 49:35,6,
                 58:52,8)

With this, If duplicate account numbers(or cusips) are present,i couldnt get unique count. UNIQUE keyword displays full report count at the end of the report. But i need two counts (acct num and cusips,without duplicates) to be printed at the end of each section.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7315

PostPosted: Wed Sep 21, 2011 3:59 pm    Post subject:
Reply with quote

Next time please use the Code tags for any data for which you want to preserve the formatting. You are more likely to get an answer if we can read the stuff....

I'm not sure that you can directly do what you want. The COUNT will be for each input record. If you have multiples on your input, they will be added to the count.

Also, you don't seem to have either account or cusip in your sort, so I'm not even sure what magic you were expecting to happen.

Questions remain: If there are multiple use of the same cusip in an account, how should it be counted; if there is multiple use of the same cusip across accounts, how should it be counted; and vice versa for both.
Back to top
View user's profile Send private message
Kiruthi R

New User


Joined: 21 Sep 2011
Posts: 4
Location: India

PostPosted: Wed Sep 21, 2011 4:19 pm    Post subject:
Reply with quote

Thanx for the reply.Yes. Thats what confusing.
So to get unique count,u mean to say there is no direct way. Is that efficient to to use temporary files.

Sort the file using sort key store it in tempfile1.
Sort the master file along wit acctnum.Remove duplicates.get count.tempfile2
Sort the master file along with cusip. Remove duplicates.tempfile3
Merge all using splice. tempfile4.
Then using cobol program,create report as per the requirement.(Requirement is : From master file , group the records using name,branch and city and report the total amount,unique count for cusip and acct-number at the end of each group in the report file.)
Is this ok or any other method to achieve this.?Please help how to get the results in indirect way.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7315

PostPosted: Wed Sep 21, 2011 4:46 pm    Post subject:
Reply with quote

I am completely open to correction on this, but from what I understand, the COUNT is going to be of the records, not of the "unique" items.

When the experts are in later, San Jose working-hours at the latest, you'll know for certain.

If it is possible, you'll certainly need to sort on the account and cusip anyway.

If you are considering a seperate report program, just your existing file with the above sorts could be enough - depending on the answers to my previous questions.

If you want accouts per break, and cusips treated as unique within account, then you would need no further than the normal sort of processing. If you want a strictly unique count of cusips (so even if present in more than one account per break, only count as one) you'd need to hold them in a table, and add each one if not already present in the table. The vice versa is higly unlikely to be your requirement, but if so, the same applies with the names changed (yes, I do know there is a posh phrase for that). And the sort key sequence, of course.

I do (strongly) suspect that along the lines you have already mentioned, a sort/tool solution is possible, but you'll need to provide clear answers.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Thu Sep 22, 2011 5:17 am    Post subject:
Reply with quote

Kiruthi R,

You don't have to send a Private message with the same question. Since you want the unique counts, it cannot be done in single pass of data. Here is a DFSORT/ICETOOL JCL which will give you the desired results. I assumed that your input is RECFM=FB and LRECL=80
Code:

//STEP0100 EXEC PGM=ICETOOL                                         
//TOOLMSG  DD SYSOUT=*                                             
//DFSMSG   DD SYSOUT=*                                             
//IN       DD DISP=SHR,DSN=Your Input File                                     
//T1       DD DSN=&&T1,DISP=(,PASS),SPACE=(CYL,(X,Y),RLSE)         
//REPORT   DD SYSOUT=*                                             
//TOOLIN   DD *                                                     
  SORT FROM(IN) USING(CTL1)                                         
  SORT FROM(T1) USING(CTL2)                                         
//CTL1CNTL DD *                                                     
  SORT FIELDS=(1,34,CH,A),EQUALS                                   
  OUTFIL FNAMES=T1,IFOUTLEN=81,                                     
  IFTHEN=(WHEN=INIT,OVERLAY=(81:C'0',SEQNUM,8,ZD,RESTART=(1,34))), 
  IFTHEN=(WHEN=(82,8,ZD,EQ,1),OVERLAY=(81:C'1'))                   
//*                                                                 
//CTL2CNTL DD *                                                     
  SORT FIELDS=(1,22,CH,A,35,6,CH,A),EQUALS                         
  OUTREC IFOUTLEN=82,IFTHEN=(WHEN=INIT,                             
  OVERLAY=(82:C'0',1,22,35,6,SEQNUM,8,ZD,RESTART=(83,28))),         
  IFTHEN=(WHEN=(111,8,ZD,EQ,1),OVERLAY=(82:C'1'))                   
                                                                   
  OUTFIL FNAMES=REPORT,                                             
  BUILD=(03:01,10,15:11,6,24:17,6,33:23,12,48:35,6,                 
         57:52,8,BI,EDIT=(IIIIIIIT.TT),80:X),                       
  SECTIONS=(1,22,SKIP=2L,                                           
   HEADER3=(03:X,/,                                                 
            03:'NAME',17:'CITY',24:'BRANCH',33:'ACCT NUM',         
            48:'CUSIPS',60:'AMOUNT',/,                             
            03:10C'-',15:6'-',24:6'-',33:12'-',48:6'-',57:11'-'),   
  TRAILER3=(03://,                                                 
            03:'TOTAL AMOUNT =',                                   
            18:TOT=(52,8,BI,EDIT=(IIIIIIIIT.TT)),/,                 
            03:' ACCT COUNT  =',                                   
            18:TOT=(81,1,ZD,M10,LENGTH=12),/,                       
            03:'CUSIP COUNT  =',                                   
            18:TOT=(82,1,ZD,M10,LENGTH=12)))                       
//*


The output from this job would be something like this
Code:

                                                                     
  NAME          CITY   BRANCH   ACCT NUM       CUSIPS      AMOUNT   
  ----------  ------   ------   ------------   ------   ----------- 
  NAME1       CITY1    BRANC1   ACCTNUM1       CUSIP1      10000.00 
  NAME1       CITY1    BRANC1   ACCTNUM2       CUSIP1       5000.00 
  NAME1       CITY1    BRANC1   ACCTNUM2       CUSIP2       5000.00 
                                                                     
  TOTAL AMOUNT =     20000.00                                       
   ACCT COUNT  =            2                                       
  CUSIP COUNT  =            2                                       
                                                                     
                                                                     
  NAME          CITY   BRANCH   ACCT NUM       CUSIPS      AMOUNT   
  ----------  ------   ------   ------------   ------   ----------- 
  NAME2       CITY2    BRANC2   ACCTNUM3       CUSIP3      14000.00 
  NAME2       CITY2    BRANC2   ACCTNUM3       CUSIP3       6000.00 
  NAME2       CITY2    BRANC2   ACCTNUM4       CUSIP4       8000.00 
  NAME2       CITY2    BRANC2   ACCTNUM4       CUSIP5       8000.00 
                                                                     
  TOTAL AMOUNT =     36000.00                                       
   ACCT COUNT  =            2                                       
  CUSIP COUNT  =            3                                       
Back to top
View user's profile Send private message
Kiruthi R

New User


Joined: 21 Sep 2011
Posts: 4
Location: India

PostPosted: Fri Sep 23, 2011 11:08 am    Post subject:
Reply with quote

Thanx Kolusu. Its working perfectly. Sorry for the late reply.So this is the logic to get two unique counts. Thanx a ton.
Thanx Bill.
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 Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts DFSORT - Overriding default option TU... Steve Ironmonger DFSORT/ICETOOL 3 Tue Apr 04, 2017 3:54 pm
No new posts Count the length of the record & ... sreekusr DFSORT/ICETOOL 4 Thu Mar 23, 2017 7:52 pm
No new posts Display FTP Session Count within TSO Yolanda Harvey TSO/ISPF 3 Fri Mar 10, 2017 10:31 pm


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