I need to consolidate the values from 1 or more rows that go to a particular student, in my case. In simple form, I might have data like:
NAME FIELD
Bob code1
Bob code2
Bob code3
Sam code1
Sam code2
Jim code4
I need the output to look like:
Bob code1,code2,code3
Jim code4
Sam code1, code2

B.J. Stepien from IBI sent the following code, but indicated some of you may have come up with a better way to do this:

SET NODATA=' '
DEFINE FILE CAR
CNTR/I1 WITH CAR = IF COUNTRY NE LAST COUNTRY THEN 1 ELSE
IF CNTR EQ 4 THEN 1 ELSE CNTR + 1;
ROW/I3 WITH CAR = IF CNTR EQ 1 THEN LAST ROW + 1 ELSE LAST ROW;
END
TABLE FILE CAR
SUM CAR ACROSS CNTR NOPRINT
BY COUNTRY AS ''
BY ROW NOPRINT
END

Thanks in advance!

This message has been edited. Last edited by: Kerry,

If the data is already in the correct order, then you can use the LAST attribute (?), as in this working example:

-* fplukins1.fex

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
SET HOLDFORMAT=ALPHA
SET COUNTWIDTH=ON
SET CENT-ZERO=ON
SET PAGE=NOPAGE
-RUN

FILEDEF FILE1 DISK file1.txt
-RUN

-WRITE FILE1 Bob code1
-WRITE FILE1 Bob code2
-WRITE FILE1 Bob code3
-WRITE FILE1 Sam code1
-WRITE FILE1 Sam code2
-WRITE FILE1 Jim code4

FILEDEF MASTER DISK file1.mas
-RUN

-WRITE MASTER FILENAME=FILE1, SUFFIX=FIX, $
-WRITE MASTER SEGNAME=FILE1, $
-WRITE MASTER FIELDNAME=DIM1, FORMAT=A4, ACTUAL=A4, $
-WRITE MASTER FIELDNAME=VAL1, FORMAT=A5, ACTUAL=A5, $
-RUN

DEFINE FILE FILE1
VALS/A200V = IF DIM1 EQ LAST DIM1 THEN VALS || (', ' | VAL1) ELSE VAL1;
END

TABLE FILE FILE1
SUM
VALS
BY DIM1
END
-RUN

If the data is not in the correct order, create a HOLD file to put the data in the required order.

(Edit: use AnV field).

This message has been edited. Last edited by: Francis Mariani,

Dan Lukins,

Is the below what you are looking for?
Please let me know.

  
TABLE FILE CAR
PRINT
BODYTYPE
BY COUNTRY
BY CAR
ON TABLE HOLD AS CAR1
END

DEFINE FILE CAR1
CONT/A1    = IF COUNTRY  NE LAST COUNTRY  THEN 'R' ELSE
             IF CAR      NE LAST CAR      THEN 'R' ELSE
             IF BODYTYPE EQ LAST BODYTYPE THEN 'Y' ELSE 'N';
SHOW/A300V = IF CONT EQ 'R' THEN BODYTYPE ELSE
             IF CONT EQ 'Y' THEN SHOW ELSE SHOW | ', ' | BODYTYPE;
END 

TABLE FILE CAR1
SUM 
SHOW
BY COUNTRY 
BY CAR 
END

Thanks,
prodrigu

to prodrigu,
Yes, I think that is what I need. It has one extra sort field, but I think I can take it out.
I'm new at this and an not sure if this is the correct way to respond to yor request.

Another solution:

TABLE FILE CAR
LIST BODYTYPE
BY CAR
ON TABLE HOLD
END
TABLE FILE HOLD
SUM BODYTYPE
BY CAR
ACROSS LIST NOPRINT
END
  

You specified needing commas to separate the values.

DEFINE FILE FILE1
VALS/A200V = IF DIM1 EQ LAST DIM1 THEN VALS || (', ' | VAL1) ELSE VAL1;
END

TABLE FILE FILE1
SUM
VALS
BY DIM1
END
-RUN