Grouping and Count Records

, ,
1

I know there has to be an easier way to do this… I have a file that has say 200 records. Part of each record contains a form #. All I I want to group and summarize how many times each form occurs in those 200 records.

So for 200 records you would get something like this:

Form # # of Occurrences
12344 100
26351 10
85963 10
63693 2
98745 78
74582 100
Total number of records = 200

Thank You!!!

2

Well, may be there is one of course, but may I ask you: why don’t you like doing this task in your OWN way? If it works properly, why do you need another solution (especially for such a small number of records)?
Any way, I would NOT look for that “easier way”:slight_smile:

Best regards.
NK

3

If your FORM number is a descriptor on the file, you can get what you want with a simple histogram:


DEFINE DATA LOCAL
1 FILE-HIST VIEW OF FILE
  2 FORM-NBR
1 #TOTAL  (I4)
END-DEFINE
*
HIST.
HISTOGRAM FILE-HIST FORM-NBR
  DISPLAY FORM-NBR *NUMBER(HIST.)
  ADD *NUMBER(HIST.) TO #TOTAL
END-HISTOGRAM
WRITE '=' #TOTAL
END

If FORM-NBR is not a descriptor, or you need to get more info from each file record, you could read the file, sort by form number and us at break logic.


DEFINE DATA LOCAL
1 FILE VIEW OF FILE
  2 FORM-NBR
END-DEFINE
*
RD.
READ FILE FORM-NBR
END-ALL
SORT BY FORM-NBR
   USING KEYS
  AT BREAK OF FORM-NBR
    DISPLAY OLD(FORM-NBR) *COUNT(FORM-NBR)
  END-BREAK
END-HISTOGRAM
WRITE '=' *COUNTER(RD.)
END
4

First, if FORM-NUMBER is a descriptor, use Jerome’s HISTOGRAM code.

If not, rather than using SORT, it might (I have not done a timing comparison yet) be faster to do something like:

DEFINE DATA LOCAL
1 FILE-HIST VIEW OF FILE
2 FORM-NBR
1 #COUNTS (I2/0:99999)
1 #LOOP (I2)
END-DEFINE
*
READ FILE-HIST
ADD 1 TO #COUNTS (FORM-NBR)
END-READ
*
FOR #LOOP = 0 to 99999
IF #COUNTS (#LOOP) NE 0
DISPLAY #LOOP #COUNTS (#LOOP)
END-IF
END-FOR
END

5

How do I change the rating I gave you?? uggg… Thank you, this is exactly … great example. 2nd is what I will need. Am I able to add a where statement in with this too? I tried and getting errors.

6

Could you show us the WHERE clause?

With a HISTOGRAM statement, you must reference a view that has only one field, namely the field to be HISTOGRAM’ed. Remember, HISTOGRAM does not read records, it only accesses the inverted tables. Thus you do not have access to other fields.

You can have a WHERE clause, but it would be something like:

HISTOGRAM MYVIEW FIELD1
WHERE FIELD1 NE ‘C’ THRU ‘G’

The WHERE clause as mentioned above could not reference a FIELD2.

If you are using the READ loop, there should be no problem with the WHERE clause. Very straightforward; for example:

READ MYVIEW WHERE FIELD2 NE ‘C’ THRU ‘G’

steve

7

Are you reading an Adabas file or a flat file?

In either case, I prefer Jerome’s SORT/BREAK method, but here’s another “array” solution.

DEFINE DATA LOCAL
1 #M (I4)     CONST <20>
1 #TBL (0:#M)
  2 #FORM (A5)
  2 #COUNT (I4)
1 #WRK
  2 #FORM (A5)
1 #E (I4)
1 #I (I4)
1 #J (I4)
END-DEFINE
DEFINE WORK FILE 1 'C:\Users\ZbrogR\Documents\NatBatch\grpcount.txt' TYPE 'ASCII'
FORMAT SG=F
READ WORK 1 #WRK
  EXAMINE #TBL.#FORM (0:#E) FOR #WRK.#FORM GIVING INDEX #I
  IF  #I = 0
    THEN
      ADD 1 TO #E
      ASSIGN #I              = #E
      ASSIGN #TBL.#FORM (#I) = #WRK.#FORM
  END-IF
  ADD 1 TO #TBL.#COUNT (#I)
  ADD 1 TO #J
END-WORK
DISPLAY #TBL.#FORM (1:#E)
        #TBL.#COUNT (1:#E)
WRITE / 'Total records:' #J (AD=L)
END

For performance, all indices should be defined as I4.

8

Thanks!!

Adabas, I got pulled away from this project and getting back on it today. I will let you know how I make out. Thank you for our help, this site is great!

===========================================