How to concatenate and drop missing values?

[letsdoit]

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!

[Haikuo]

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

[letsdoit]

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;

[Haikuo · Accepted solution]

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

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

[Tom]

CATX() function does that already.

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

[Haikuo]

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

[letsdoit]

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!

[Tom]

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 ;

[letsdoit]

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