Concat string in group by

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

Concat string in group by

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?


Accepted Solutions
Solution
‎09-12-2016 09:33 AM
Contributor
Posts: 28

Re: Concat string in group by

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.

 

View solution in original post


All Replies
Respected Advisor
Posts: 4,948

Re: Concat string in group by

What if your data is slightly different?

 

Name Number

'A'       1

'B'       1

'A'       1

'C'       2

 

What should the result be?

 

 

Contributor
Posts: 28

Re: Concat string in group by

Name Number

'ABA'  1

'C'       2

Solution
‎09-12-2016 09:33 AM
Contributor
Posts: 28

Re: Concat string in group by

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.

 

Grand Advisor
Posts: 17,294

Re: Concat string in group by

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

Contributor
Posts: 28

Re: Concat string in group by

What if I want a newline?

Esteemed Advisor
Posts: 6,634

Re: Concat string in group by


WouterKBC wrote:

What if I want a newline?


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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 28

Re: Concat string in group by

[ Edited ]

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!

Super User
Posts: 1,225

Re: Concat string in group by

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;

           

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 532 views
  • 1 like
  • 5 in conversation