0

I need to filter latest records for each employee in PS file. How can I achieve this using DFSORT?

Records in PS file as Employee id(6 digits) and date(8 digits). I need to filter latest records of each employee keeping the original order that the employees appeared in.

example : i/p file:

10000120150101
10000320130101
10000120160101
10000220170101
10000220160101
10000320160101

o/p file:

10000120160101
10000320160101
10000220170101

Please share the answer too if you are not getting the same order but latest records.

Bill Woodger
  • 12,968
  • 4
  • 38
  • 47
Jitesh Agarwal
  • 11
  • 1
  • 1
    Do you want the original order or not? If not concerned about the original order, then @cschnied's answer is fine. If you are concerned about the original order, why bother with the last part of your question? – Bill Woodger Nov 29 '16 at 15:22
  • Ah.. I meant @SaggingRufus. Sorry about that. – Bill Woodger Nov 30 '16 at 03:23

1 Answers1

1
//SYSIN   DD *
 SORT FIELDS=(1,6,CH,A,
              7,8,CH,D)
 SUM FIELDS=NONE
/*

The Y4T type signifies that you are sorting a YYYYMMDD date. So this sort will keep all of the employee records together while putting the most recent record for that employee first.

EDIT: changed last line of the SORT to 7,8,CH as this will still sort correctly and not have additional overhead

SaggingRufus
  • 1,814
  • 16
  • 32
  • Sorry, confused you with someone else :-) You can't use Y4T like that in a SORT, it would need to be CH (best, or BI, which would be equivalent but confuse many). You could have used Y2T, which would I guess have the overhead of "windowing" for working out the correct collating sequence. – Bill Woodger Nov 30 '16 at 03:22
  • @BillWoodger I made the suggested edit. For my own knowledge, why would Y2T work and not Y4T? I was under the impression that they were just different formats, but should function the same. – SaggingRufus Nov 30 '16 at 11:43
  • Y2T uses the "year window" to know that 01 should sort "higher" than 99. This is meaningless for a YYYYMMDD format which can just be sorted correctly with CH. – Bill Woodger Nov 30 '16 at 17:40
  • And now I've looked at the edit. Although I think the 9,6,Y2T will work (assuming that a reasonable year-window has been established at DFSORT installation at the site) it will be better to use 7,8,CH,D. There is nothing "special" about a YYYYMMDD date which would require something special to sort correctly, it is just naturally in a sortable order of the sub-fields (my favourite date format when months are needed). There will be additonal "overhead" with the Y2T, both in processing and in extra storage required for the sort key. – Bill Woodger Nov 30 '16 at 17:43