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
 

 

For each given key value, how to keep up to the first n recs

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

New User


Joined: 08 Apr 2005
Posts: 43

PostPosted: Fri Nov 11, 2016 11:38 pm    Post subject: For each given key value, how to keep up to the first n recs
Reply with quote

I have a file where each input line consists of:

Country, City, Population

I want to do a sort on the file so that for each country I keep the three records of the most populous cities. Sometimes I will have less then three cities for a country to start with, sometimes more, and sometimes exactly three.


So for this input:

Code:
CAN CIT1 25
CAN CIT2 16
CAN CIT3 15
CAN CIT4 14
CAN CIT5 13
GBR CITA 65
GBR CITB 45
GBR CITC 12
USA CITX 66
USA CITZ 45


I would want to end up with:

Code:
CAN CIT1 25
CAN CIT2 16
CAN CIT3 15
GBR CITA 65
GBR CITB 45
GBR CITC 12
USA CITX 66
USA CITZ 45



I've provided the records already sorted in descending population with respect to each Country. In case it is relevant, I know that all Country names are unique and all City names are unique (ie can not occur from Country to Country). From any run to another I will not know what Countries or Cities I will encounter.

Thanks for any help and I apologize in advance if this has a simple solution, I have tried to find answer on my own but got nowhere.
Back to top
View user's profile Send private message

Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Fri Nov 11, 2016 11:53 pm    Post subject:
Reply with quote

Quote:
I keep the three records of the most populous cities
Is there any logic to pick popular cities else you can try below approach.
You can group by country and sequence number and restart the sequence number for next Group and so on and in OUTFIL filter to include up 3 records per group.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2164
Location: @my desk

PostPosted: Sat Nov 12, 2016 12:02 am    Post subject:
Reply with quote

Rohit Umarjikar wrote:
Quote:
I keep the three records of the most populous cities
Is there any logic to pick popular cities else you can try below approach.
Rohit - He needs the most 'populous', highest to least population.

Regardless, the idea is more or less the same. Sort descending on the population for each country, Assign a SEQuence number in the OUTREC for each GROUP, where KEYBEGINs when country changes. And in OUTFIL INCLUDE SEQuences less than or equals 3.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Sat Nov 12, 2016 12:14 am    Post subject:
Reply with quote

Code:
//SYSIN DD *                                                   
      SORT FIELDS=(1,3,CH,A,10,2,ZD,D)                         
      OUTREC IFTHEN=(WHEN=INIT,                               
                      OVERLAY=(81:SEQNUM,2,ZD,RESTART=(1,3))) 
      OUTFIL FNAMES=SORTOUT,INCLUDE=(81,2,ZD,LE,3),BUILD=(1,80)
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Sat Nov 12, 2016 12:15 am    Post subject: Reply to: For each given key value, how to keep up to the first n recs
Reply with quote

Div Grad, If you already have the population in descending order then this will do:
Code:
 INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,3),PUSH=(20:SEQ=1))
 OUTFIL INCLUDE=(20,1,ZD,LE,3),BUILD=(1,11)
 OPTION COPY


If you want to sort population in descending order first then:
Code:
 SORT FIELDS=(1,3,CH,A,10,2,ZD,D)
 OUTREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,3),PUSH=(20:SEQ=1))
 OUTFIL INCLUDE=(20,1,ZD,LE,3),BUILD=(1,11)


btw.. apologies accepted --)

.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2164
Location: @my desk

PostPosted: Sat Nov 12, 2016 12:41 am    Post subject:
Reply with quote

And when you assign a sequence number, make sure it can accommodate 'more people'. Something like
Code:
SEQ=8

in your actual control statements.
Back to top
View user's profile Send private message
Div Grad

New User


Joined: 08 Apr 2005
Posts: 43

PostPosted: Sat Nov 12, 2016 2:24 am    Post subject:
Reply with quote

I went with RahulG31's option and it worked like a charm!

Thanks so much everyone!
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7234

PostPosted: Sat Nov 12, 2016 2:44 am    Post subject: Reply to: For each given key value, how to keep up to the first n recs
Reply with quote

If your data is already in order, just use a SEQNUM with RESTART for the key and the INCLUDE= and BUILD on the OUTFIL. Your sequence number should be large enough for the maximum number of cities. One is probably cutting it fine, eight is a bit much :-)
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 Sort,remove & capture dup recs ha... subratarec DFSORT/ICETOOL 21 Tue Apr 28, 2015 9:28 pm
No new posts ICETOOL - Unable to process more than... Div Grad DFSORT/ICETOOL 5 Tue Dec 02, 2014 12:52 am
No new posts How to write out all dropped recs fro... nkrishnamoorthy DFSORT/ICETOOL 3 Fri Nov 29, 2013 12:58 am
No new posts File Recs across multiple "lines... John Del DFSORT/ICETOOL 4 Fri Nov 08, 2013 10:54 pm
No new posts Get unmatched and matched recs with a... maki_psg JCL & VSAM 3 Thu Oct 24, 2013 11:09 am


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