topic Re: Concat string in group by in SAS Programming

Concat string in group by

WouterKBC — Mon, 12 Sep 2016 13:00:12 GMT

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?

Re: Concat string in group by

Astounding — Mon, 12 Sep 2016 13:03:38 GMT

What if your data is slightly different?

Name Number

'A' 1

'B' 1

'A' 1

'C' 2

What should the result be?

Re: Concat string in group by

WouterKBC — Mon, 12 Sep 2016 13:09:39 GMT

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.

Re: Concat string in group by

WouterKBC — Mon, 12 Sep 2016 13:10:28 GMT

Name Number

'ABA' 1

'C' 2

Re: Concat string in group by

Reeza — Mon, 12 Sep 2016 13:16:01 GMT

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

Re: Concat string in group by

WouterKBC — Mon, 12 Sep 2016 13:19:37 GMT

What if I want a newline?

Re: Concat string in group by

Kurt_Bremser — Mon, 12 Sep 2016 13:29:41 GMT

@WouterKBC wrote:

What if I want a newline?

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

Re: Concat string in group by

WouterKBC — Mon, 12 Sep 2016 13:33:38 GMT

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!

Re: Concat string in group by

Shmuel — Mon, 12 Sep 2016 13:34:45 GMT

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;