BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
WouterKBC
Obsidian | Level 7

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?

1 ACCEPTED SOLUTION

Accepted Solutions
WouterKBC
Obsidian | Level 7

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

8 REPLIES 8
Astounding
PROC Star

What if your data is slightly different?

 

Name Number

'A'       1

'B'       1

'A'       1

'C'       2

 

What should the result be?

 

 

WouterKBC
Obsidian | Level 7

Name Number

'ABA'  1

'C'       2

WouterKBC
Obsidian | Level 7

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
Super User

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

WouterKBC
Obsidian | Level 7

What if I want a newline?

WouterKBC
Obsidian | Level 7

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
Garnet | Level 18

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;

           

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 13275 views
  • 2 likes
  • 5 in conversation