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!!!
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.