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!
if not missing (ct) then cat=catx(", ",cat,ct);
Tom's suggestion also works, if you run this first: options missing='';
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
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;
if not missing (ct) then cat=catx(", ",cat,ct);
Tom's suggestion also works, if you run this first: options missing='';
CATX() function does that already.
new = catx(',' , of col1-col4);
if col1-col4 are Char., or Options missing='';
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!
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 ;
Thank you both so much! Both suggestions work perfect!!!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.