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;
You need to define the new variable with a sufficient length, or it will default to the length of W and be too short.
How many rows do you want in the final output? 2 or 5?
@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)
5 (same as in original data set)
You need to define the new variable with a sufficient length, or it will default to the length of W and be too short.
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;
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.