I have a table like this:
Name Number
'A' 1
'B' 1
'C' 2
I would like to have a result like:
Name Number
'AB' 1
'C' 2
How do I do this? You can't just call cat(Name) with a group by Number in a proc sql function right?
Accidently found the solution.
data want (drop=name_old);
set tmp (rename=(name=name_old));
by number;
retain string;
length string $ 400; * set large enough to accommodate the maximum number of records per ID;
if first.number then string = '';
string = catx(',',trim(string),name_old);
if last.number then output;
run;
Apparently 'concat' is not something that is native in proc sql.
What if your data is slightly different?
Name Number
'A' 1
'B' 1
'A' 1
'C' 2
What should the result be?
Name Number
'ABA' 1
'C' 2
Accidently found the solution.
data want (drop=name_old);
set tmp (rename=(name=name_old));
by number;
retain string;
length string $ 400; * set large enough to accommodate the maximum number of records per ID;
if first.number then string = '';
string = catx(',',trim(string),name_old);
if last.number then output;
run;
Apparently 'concat' is not something that is native in proc sql.
You don't need CATX for your requirements use CATT if you don't want commas.
What if I want a newline?
@WouterKBC wrote:
What if I want a newline?
Use the catx() function with '0a'x as separator.
Doesn't do anything. It just puts everything on 1 line...
string = catx('0a'x,trim(string),name_old);
Edit: It does. It just doesn't show in the table view in SAS. A copy of the value shows a new line.
Thanks!
data have;
input name number ;
datalines;
A 1
B 1
C 2
;
run;
proc sort data=have; by number name; run;
data want(rename=(name_out=name));
retain name_out;
length name_out $10; /* maximum amount of cocatenations expected */
set have;
by number name;
if first.number then name_out=name;
else name_out = cat(trim(name_out),name); /* assuming no repeats of name in group */
if last.number then output;
drop name;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.