DATA Step, Macro, Functions and more

How do I concatenate several text columns while ignoring missing values?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

How do I concatenate several text columns while ignoring missing values?

Hi,

 

Here is what my data looks like:

 

Unique ID      Col1      Col2     Col3     Col4

   1                Text1                  Text3

   2                Text1     Text2                 Text4

   3

   4                              Text2     Text3

 

I'm trying to create a new column that lists all the text from column 1 through 4, with commas and a single between the different texts, and that shows nothing when there is no text. Here's what I want:

 

Unique ID     NewCol

   1                Text1, Text3

   2                Text1, Text2, Text4

   3

   4                Text2, Text3

 

I can't seem to figure out a way with out creating ", , ," for line 3, and extra commas in the text strings (this creates a problem since I actually have 29 columns of text I'm trying to put in a text sting in a single column).

 

Any suggestions?

 

Amanda

 


Accepted Solutions
Solution
‎12-30-2016 02:17 PM
Super User
Posts: 10,500

Re: How do I concatenate several text columns while ignoring missing values?

In a datastep

 

new_col = catx(',',col1, col2,col3,col4);

 

the first argument in the catx function is the character(s) to place between the values indicated. Blanks are suppressed

View solution in original post


All Replies
Solution
‎12-30-2016 02:17 PM
Super User
Posts: 10,500

Re: How do I concatenate several text columns while ignoring missing values?

In a datastep

 

new_col = catx(',',col1, col2,col3,col4);

 

the first argument in the catx function is the character(s) to place between the values indicated. Blanks are suppressed

Occasional Contributor
Posts: 13

Re: How do I concatenate several text columns while ignoring missing values?

Works perfect! Thank you for your help!


Occasional Contributor
Posts: 13

Re: How do I concatenate several text columns while ignoring missing values?

Works perfect and so simple! Thank you.

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 241 views
  • 0 likes
  • 2 in conversation