How to add all the columns at once in a CATS function instead of writing them one by one

Question

I have the following data:

data WORK.TEMP_PTCAR_1;
 input ID $ COORDINATES $ 5-7;
 cards;
ID1 1 5 
ID1 4 8 
ID1 8 7 
ID2 3 4 
ID2 9 2
;
run;

ID	COORDINATES
ID1	1 5
ID1	4 8
ID1	8 7
ID2	3 4
ID2	9 2

And would like to have this:

ID	COORDINATES
ID1	LINESTRING(1 5, 4 8, 8 7)
ID2	LINESTRING(3 4, 9 2)

I tried with the following line of code:

PROC TRANSPOSE DATA=WORK.TEMP_PTCAR_1
    OUT=WORK.TEMP_PTCAR_2(LABEL="TEMP_PTCAR_2")
    PREFIX=XY
    NAME=Source
    LABEL=Label;
    BY ID ;
    VAR COORDINATES;
RUN;

Then I tried to create a custom column to show the linestring:

("LINESTRING(" || CATX(", ", t1.XY1,t1.XY2) || ")") AS Calculation

But the problem is that I have to manually fill in all the columns (XY1, XY2, XY3,...) in the CATX function.

Is there another way?

Any help is highly appreciated ;)

Thanks!

Answer 1

You can get the required result using the DATA step with BY group processing. Remember to use a length statement to ensure the target variable is long enough to contain the result.

data TEMP_PTCAR_2;
    set TEMP_PTCAR_1;
    length new_coord $ 200;
    by ID;
    retain new_coord;
    if first.ID then new_coord = "LISTING("||coordinates;
    else new_coord = trim(new_coord)||","||coordinates;
    if last.ID then do;
        new_coord = trim(new_coord)||")";
        output;
    end;
    drop coordinates;
    rename new_coord = coordinates;
run;