topic Re: concatenate over rows and then remerge it in SAS Programming

concatenate over rows and then remerge it

Ronein — Wed, 06 Nov 2019 10:02:26 GMT

Hello

I have a row data that contain multiple rows per customer.

I want to concatenate the values in column "Loc" and the remerge it with original data set

Data tbl;
input ID  Loc $;
cards;
1 .
1 .
1 3
1 .
1 5
2 .
2 .
2 3
2 .
2 .
3 .
3 .
3 .
3 .
3 .
;
run;
/*Required data set*/
/*1 .  3,5*/
/*1 .  3,5*/
/*1 3  3,5*/
/*1 .  3,5*/
/*1 5  3,5*/
/*2 .  3*/
/*2 .  3 */
/*2 3  3*/
/*2 .  .*/
/*2 .  .*/
/*3 .  .*/
/*3 .  .*/
/*3 .  .*/
/*3 .  .*/
/*3 .  .*/

Re: concatenate over rows and then remerge it

PeterClemmensen — Wed, 06 Nov 2019 10:10:05 GMT

@Ronein did you find your answer to your previous thread?

https://communities.sas.com/t5/SAS-Programming/calculate-number-of-distinct-values-between-rows-for-each/m-p/601887

If so, please remember to close the thread.

Re: concatenate over rows and then remerge it

PeterClemmensen — Wed, 06 Nov 2019 10:16:12 GMT

data want;
    do until (last.id);
        set tbl;
        length con $ 200;
        by id;
        if not missing(Loc) then con=catx(',', con, Loc);
    end;
    do until (last.id);
        set tbl;
        by id;
        output;
    end;
run;

Result:

ID   Loc   con
1          3,5
1          3,5
1    3     3,5
1          3,5
1    5     3,5
2    3
2    3
2    3     3
2    3
2    3
3  
3  
3  
3  
3