I have a requirement where, I need to concatenate all the values of a field under a group.

In the below CAR example

TABLE FILE CAR
PRINT MODEL
BY CAR
END

I need to concatenate all the values of MODEL under a CAR type.

I tried the following way

TABLE FILE CAR
PRINT MODEL
COMPUTE MOD2/A200 = IF CAR EQ LAST CAR THEN MODEL | LAST MOD2 ELSE MODEL;
-*COMPUTE MOD3/A300 = IF CAR EQ LAST CAR THEN MODEL | LAST MODEL| LAST MOD2 ELSE MODEL;
-*COMPUTE MOD4/A400 = IF CAR EQ LAST CAR THEN LAST MOD3 | MODEL ELSE MODEL;
BY CAR
END

But this is not possible as always there will be conflict in the field size.

Can anyone give me some other logic for this.

You can not do it using a define or compute as by concatenating to itself
you automatically have more data than the field can accommodate.

Your only real option is to dynamically rewrite the data and create a
A master to use the new data file.

look at the following as a guide.

FILEDEF NEWHOLD DISK NEWHOLD.FTM (APPEND
-RUN
FILEDEF NEWHOLDM DISK NEWHOLD.MAS
-RUN
TABLE FILE CAR
PRINT MODEL
BY CAR
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS H1 FORMAT ALPHA
END
-RUN
-SET &LOOPS = &LINES;
-SET &LASTCAR = ' ';
-SET &MODELCARS = ' ';
-REPEAT ENDREPEAT &LOOPS TIMES

-READ H1 NOCLOSE &CAR.A10. &MODEL.A24.
-IF '&LASTCAR.EVAL' EQ ' ' OR '&LASTCAR.EVAL' EQ '&CAR.EVAL' GOTO SKIPWRITE;
-WRITE NEWHOLD &MODELCARS
-SKIPWRITE
-SET &MODELCARS= IF '&CAR.EVAL' NE '&LASTCAR.EVAL' THEN '&CAR.EVAL' | '&MODEL.EVAL' ELSE
- '&MODELCARS.EVAL' | '&MODEL.EVAL';
-SET &LASTCAR='&CAR.EVAL';
-ENDREPEAT
TABLE FILE CAR
SUM CNT.MODEL
BY CAR
ON TABLE HOLD AS H2
END
-RUN
TABLE FILE H2
SUM MAX.MODEL
ON TABLE SAVE
END
-RUN
-READ SAVE &MAXCARS.A5.
-SET &MODLEN=24*&MAXCARS;
-WRITE NEWHOLDM FILENAME=NEWHOLD , SUFFIX=FIX , $
-WRITE NEWHOLDM SEGMENT=NEWHOLD, SEGTYPE=S1, $
-WRITE NEWHOLDM FIELDNAME=CAR, ALIAS=E01, USAGE=A16, ACTUAL=A16, $
-WRITE NEWHOLDM FIELDNAME=MODELS, ALIAS=E02, USAGE=A&MODLEN.EVAL, ACTUAL=A&MODLEN.EVAL, $
-RUN
TABLE FILE NEWHOLD
PRINT *
END

1. Find the Count of models per each car
2. Out of this get the max value of this count among all cars into a variable say &MAXMODELS. Now your mod2 field should be of minimum size
&MINSIZE = &MAXMODELS * Size of the model field

3. COMPUTE MOD2/A200 =IF CAR EQ LAST CAR THEN MODEL | LAST MOD2 ELSE MODEL; is recursive so you always have problem. Instead use a trim on the second part of the expression (i.e. LAST MOD2).

Idea can be for a type of car, the max size of the last mod2 can be (&MAXMODELS -1) * Size of the model
&PR_MAX_SIZE= (&MAXMODELS-1) * Size of the model field

So this compute field can be something like
IF CAR EQ LAST CAR THEN MODEL | TRIM('T', LAST MOD2, &MINSIZE.EVAL, ' ', 1, 'A&PR_MAX_SIZE.EVAL')ELSE MODEL;

here is your other possible code
----------------------------
TABLE FILE CAR
COUNT MODEL
BY CAR
ON TABLE HOLD AS HLD1 FORMAT ALPHA
END
FILEDEF MAXCNT DISK CNT_H.TXT
-RUN
TABLE FILE HLD1
PRINT MODEL
BY HIGHEST 1 MODEL NOPRINT
ON TABLE HOLD AS MAXCNT FORMAT ALPHA
END
-RUN
-READ MAXCNT &HVAL.I5
-* max Size of the mod2 field
-SET &NEWVAL= &HVAL * 24 ;
-SET &OLDVAL= (&HVAL-1) * 24 ;
-SET &A1_SIZE= 'A' | '&NEWVAL';
-SET &A2_SIZE= 'A' | '&OLDVAL';

TABLE FILE CAR
PRINT MODEL
COMPUTE MOD2/&A1_SIZE.EVAL = IF CAR EQ LAST CAR THEN MODEL | TRIM('T', LAST MOD2,&NEWVAL.EVAL, ' ', 1, '&A2_SIZE.EVAL') ELSE MODEL;
BY CAR
END

Another method would be to set NODATA to nothing and then table out the car file using the MODEL in an across NOPRINT. Then declare your own master file with the single secondary field set to the field length you need to encompass the entire width of the values (or use J.G.'s method to determine it). This would only work for HTML where white space is, by default, squeezed -

APP FI MODELS DISK MYMODELS.MAS
SET NODATA = ''
-RUN
-WRITE MODELS
-WRITE MODELS FILE=MYMODELS,SUFFIX=FIX
-WRITE MODELS SEGNAME=SEG1
-WRITE MODELS FIELD=COUNTRY, ALIAS=E01, USAGE=A10,  ACTUAL=A10, $
-WRITE MODELS FIELD=MODEL,   ALIAS=E02, USAGE=A486, ACTUAL=A486, $
-RUN
DEFINE FILE CAR
  MYMODEL/A27 = MODEL | ' , ';
END
TABLE FILE CAR
SUM MYMODEL
BY COUNTRY
ACROSS MODEL NOPRINT
ON TABLE SAVE AS TEMPHOLD FORMAT ALPHA
END
-RUN

APP FI MYMODELS DISK TEMPHOLD.FTM
-RUN

TABLE FILE MYMODELS
PRINT MODEL
BY COUNTRY
END
-RUN

T

Sridevi:

In order to avoid field size conflict, use SUBSTR function.
Try the following example: (formats: A200 - A24 = A176)

TABLE FILE CAR
  PRINT 
    MODEL 
    COMPUTE MOD2/A200 = 
      IF CAR EQ LAST CAR THEN MODEL | SUBSTR(200, LAST MOD2, 1, 176, 176, 'A176') 
      ELSE MODEL ;
  BY CAR
END

Regards,
Mikel

Thanks a lot for all your ideas.
Mikel's idea was very simple.It worked out great!!

quote:

Originally posted by Mikel:
Sridevi:

In order to avoid field size conflict, use SUBSTR function.
Try the following example: (formats: A200 - A24 = A176)

TABLE FILE CAR
  PRINT 
    MODEL 
    COMPUTE MOD2/A200 = 
      IF CAR EQ LAST CAR THEN MODEL | SUBSTR(200, LAST MOD2, 1, 176, 176, 'A176') 
      ELSE MODEL ;
  BY CAR
END

Regards,
Mikel

Mike,

I ran across your post from 2006 and it helped me immensely...I had a quick question - where in this compute statement could I add an ; between each of the values?

Thanks,
Cathie

To add a punctuation between, just concatenate it in:

quote:

COMPUTE MOD2/A200 =
IF CAR EQ LAST CAR THEN MODEL |'; '| SUBSTR(200, LAST MOD2, 1, 174, 174, 'A174')
ELSE MODEL ;