concatenate values across observations

[Ronein]

Hello

I want to concatenate values across observations.

The results are not as I want .

For ID=1 I expect to get in last row  175X,152X,280X

For ID=2 I expect to get in last row  921X,929X

Data raw_tbl;
Informat Date_of_Birth  mmddyy10.;
Format Date_of_Birth  date9.;
INPUT ID	Date_of_Birth	X $	W $;
cards;
1 9/15/2010 60118 175X
1 9/15/2010 60118 152X
1 9/16/2010 60119 280X
2 5/18/1999 60321 921X
2 5/18/1999 60321 929X
;
run;

data required ;
set raw_tbl;
by ID;
retain Concatenate_W ;
IF first.ID then Concatenate_W=W;
else Concatenate_W = catx(',',Concatenate_W,W);
run;

[PeterClemmensen]

How many rows do you want in the final output? 2 or 5?

[andreas_lds]

---

@PeterClemmensen wrote:

How many rows do you want in the final output? 2 or 5?

---

or 7 (summing row at the end of each group)

[Ronein]

 5  (same as in original data set)

[Kurt_Bremser · Accepted solution]

You need to define the new variable with a sufficient length, or it will default to the length of W and be too short.

[s_lassen]

The problem is that because of the first assignment statement, 

IF first.ID then Concatenate_W=W;

your contatenated variable gets the same length (8) as the W  variable.

BTW, I would use CALL CATX for that kind of work, as it is faster (instead of concatenating into a temporary space and then moving all the data to the target variable, the data i directly concatenated into the target variable), e.g.:

data required ;
  do until(last.ID);
    set raw_tbl;
    by ID;
    length Concatenate_W $100;
    call catx(',',Concatenate_W,W);
    output;
    end;
run;

[ed_sas_member]

Hi @Ronein 

I have added a data step to calculate automatically the maximum length needed for you new variable Concatenate_W.

It is stored in the macrovariable &max_length.

Best,

/* Compute the maximial length for each ID */
data raw_tbl_c;
	set raw_tbl;
	by ID;
	if first.ID then len_W = 0;
	len_W + length(W) + 1; /* add 1 for the comma */
	if last.ID then do;
		len_W_final = len_W - 1; /* remove the last comma from length calculation*/
		output;
	end;
run;

/* Put the maximal length among all IDs into a macrovariable &max_length */
proc sql noprint;
	select max(len_W_final) into:max_length from raw_tbl_c;
quit;

/* Concatenate values and set Concatenate_W length to &max_length */
data required;
	set raw_tbl;
	length Concatenate_W $&max_length.;
	by ID;
	retain Concatenate_W;

	IF first.ID then Concatenate_W=W;
	else Concatenate_W=catx(',', Concatenate_W, W);
run;

proc print data=raw_tbl;
run;

proc print data=required;
run;