How to concatenate and drop missing values?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

How to concatenate and drop missing values?

I have data that looks like this:

Date               Col1     Col2          Col3     Col4

6/1/2012          20

6/5/2012          20          25               27

6/10/2012        25          48

6/15/2012        38          42               43     50

How can I concatenate the data from the different columns into one to look like this:

6/1/2012          20

6/5/2012          20, 25, 27

6/10/2012        25, 48

6/15/2012        38, 42, 43, 50

and not like this

6/1/2012          20, ., ., .

6/5/2012          20, 25, 27, .

6/10/2012        25, 48, ., .

6/15/2012        38, 42, 43, 50

Thanks for your help!


Accepted Solutions
Solution
‎08-27-2013 12:05 PM
Respected Advisor
Posts: 3,124

Re: How to concatenate and drop missing values?

if not missing (ct) then cat=catx(", ",cat,ct);

Tom's suggestion also works, if you run this first: options missing='';

View solution in original post


All Replies
Respected Advisor
Posts: 3,124

Re: How to concatenate and drop missing values?

One way to do it is to check if missing before the concatenation:

data have;

infile cards truncover;

input Date  mmddyy10.      Col1 Col2          Col3     Col4;

array ct col:;

length cat $ 50;

do over ct;

if not missing (ct) then cat=catx(',',cat,ct);

end;

cards;

6/1/2012          20

6/5/2012          20          25               27

6/10/2012        25          48

6/15/2012        38          42               43     50

;

Haikuo

Occasional Contributor
Posts: 17

Re: How to concatenate and drop missing values?

Thanks for the fast response. I don't want to use "cards" my data is pretty big. When I copied over and run your code it is working but when I tried it with this code its not working. Any ideas what I am doing wrong?

data data_2;

     set data_1;

     array ct COL:;

     length cat $ 50;

     do over ct;

     if not missing (ct) then cat=catx(", ",cat,COL);

     end;

     run;

Solution
‎08-27-2013 12:05 PM
Respected Advisor
Posts: 3,124

Re: How to concatenate and drop missing values?

if not missing (ct) then cat=catx(", ",cat,ct);

Tom's suggestion also works, if you run this first: options missing='';

Super User
Super User
Posts: 6,500

Re: How to concatenate and drop missing values?

CATX() function does that already.

new = catx(',' , of col1-col4);

Respected Advisor
Posts: 3,124

Re: How to concatenate and drop missing values?

if col1-col4 are Char., or Options missing=''; 

Occasional Contributor
Posts: 17

Re: How to concatenate and drop missing values?

This is what I had initially

CATX() function does that already. new = catx(',' , of col1-col4);

but the final result is "20, ., ., ." and I don't want the ", ., ." if it is missing

thanks!

Super User
Super User
Posts: 6,500

Re: How to concatenate and drop missing values?

So just add the OPTIONS statement.  It is a good habit to reset the option afterwords.

%let save="%sysfunc(getoption(missing))";

options missing=' ';

data ...

  new = catx(',',of col1-col4);

...

options missing=&save ;

Occasional Contributor
Posts: 17

Re: How to concatenate and drop missing values?

Thank you both so much! Both suggestions work perfect!!!

☑ This topic is SOLVED.

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

Discussion stats
  • 8 replies
  • 273 views
  • 6 likes
  • 3 in conversation