Concat string in group by

[WouterKBC]

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?

[Astounding]

What if your data is slightly different?

Name Number

'A'       1

'B'       1

'A'       1

'C'       2

What should the result be?

[WouterKBC]

Name Number

'ABA'  1

'C'       2

[WouterKBC · Accepted solution]

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.

[Reeza]

You don't need CATX for your requirements use CATT if you don't want commas. 

[WouterKBC]

What if I want a newline?

[Kurt_Bremser]

---

@WouterKBC wrote:

What if I want a newline?

---

Use the catx() function with '0a'x as separator.

[WouterKBC]

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!

[Shmuel]

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;