BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
letsdoit
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

8 REPLIES 8
Haikuo
Onyx | Level 15

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
Fluorite | Level 6

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
Onyx | Level 15

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

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

Tom
Super User Tom
Super User

CATX() function does that already.

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

Haikuo
Onyx | Level 15

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

letsdoit
Fluorite | Level 6

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
Super User Tom
Super User

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
Fluorite | Level 6

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 5715 views
  • 8 likes
  • 3 in conversation