topic concatenate values across observations in SAS Programming

concatenate values across observations

Ronein — Wed, 11 Dec 2019 09:46:05 GMT

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;

Re: concatenate values across observations

PeterClemmensen — Wed, 11 Dec 2019 09:53:24 GMT

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

Re: concatenate values across observations

Kurt_Bremser — Wed, 11 Dec 2019 10:02:12 GMT

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

Re: concatenate values across observations

andreas_lds — Wed, 11 Dec 2019 10:30:14 GMT

@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)

Re: concatenate values across observations

Ronein — Wed, 11 Dec 2019 10:36:35 GMT

5 (same as in original data set)

Re: concatenate values across observations

s_lassen — Wed, 11 Dec 2019 11:13:20 GMT

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;

Re: concatenate values across observations

ed_sas_member — Wed, 11 Dec 2019 11:21:53 GMT

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;