IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

For each given key value, how to keep up to the first n recs
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
Div Grad

New User


Joined: 08 Apr 2005
Posts: 45
PostPosted: Fri Nov 11, 2016 11:38 pm
 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

Global Moderator


Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
PostPosted: Fri Nov 11, 2016 11:53 pm
 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: 2481
Location: @my desk
PostPosted: Sat Nov 12, 2016 12:02 am
 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

Global Moderator


Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
PostPosted: Sat Nov 12, 2016 12:14 am
 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: 446
Location: USA
PostPosted: Sat Nov 12, 2016 12:15 am
 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: 2481
Location: @my desk
PostPosted: Sat Nov 12, 2016 12:41 am
 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: 45
PostPosted: Sat Nov 12, 2016 2:24 am
 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

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
PostPosted: Sat Nov 12, 2016 2:44 am
 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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 

Similar Topics
Topic Forum Replies
No new posts Creating CSV file from Variable recs ... DFSORT/ICETOOL 11
No new posts IFTHEN HIT=NEXT BUILR should create m... DFSORT/ICETOOL 5
No new posts How to include a particular field val... DFSORT/ICETOOL 8
No new posts How to write Specific Fields from Mul... SYNCSORT 6
No new posts Can you write 2 recs on SORTOUT when ... DFSORT/ICETOOL 6
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us