topic Re: How to concatenate and drop missing values? in SAS Programming

How to concatenate and drop missing values?

letsdoit — Tue, 27 Aug 2013 14:54:25 GMT

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!

Re: How to concatenate and drop missing values?

Haikuo — Tue, 27 Aug 2013 15:38:21 GMT

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

Re: How to concatenate and drop missing values?

Tom — Tue, 27 Aug 2013 15:40:12 GMT

CATX() function does that already.

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

Re: How to concatenate and drop missing values?

Haikuo — Tue, 27 Aug 2013 15:45:28 GMT

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

Re: How to concatenate and drop missing values?

letsdoit — Tue, 27 Aug 2013 15:57:40 GMT

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;

Re: How to concatenate and drop missing values?

letsdoit — Tue, 27 Aug 2013 16:02:10 GMT

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!

Re: How to concatenate and drop missing values?

Haikuo — Tue, 27 Aug 2013 16:05:36 GMT

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

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

Re: How to concatenate and drop missing values?

Tom — Tue, 27 Aug 2013 16:09:17 GMT

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 ;

Re: How to concatenate and drop missing values?

letsdoit — Tue, 27 Aug 2013 16:26:53 GMT

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