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

I have 1 table that has 30 number columns that have various numbers and I want to concatenate them into 1 column with no spaces or commas or anything.

The table again has 30 number columns that begin with ReAdm1, ReAdm2, ReAdm3 all the way to ReAdm30

Each column contains a number and my goal is to have all the numbers appear in 1 column

I can name it Readmits

For example

ReAdm1          ReAdm2          ReAdm3            all the way to ReAdm30

12                    15                       7                                        16

Want the final to be 1 column that looks like this

ReAdm

1215716

I have tried like data merge and concatenating with ReAdm1 || ReAdm2 || but that does not work

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

Editor's note: Thanks to all who gave code samples to accomplish this task.  This answer is the most simplified one that achieves the goal.

 

You can also combine part of the two previous suggestions and use:

 

data have;

input ReAdm1          ReAdm2          ReAdm3            ReAdm4;

cards;

12                    15                       7                                        16

;

 

data want (keep=Readmits);

  set have;

  Readmits=cats(of _numeric_);

run;

View solution in original post

12 REPLIES 12
Haikuo
Onyx | Level 15

Try this out:

data have;

input ReAdm1          ReAdm2          ReAdm3            ReAdm4;

cards;

12                    15                       7                                        16

;

data want (keep=Readmits);

set have;

length Readmits $30.;

array num _numeric_;

do     over num;

  if not missing(num) then

   Readmits=cats(Readmits,num);

end;

run;

It works on all of your numeric variables regardless of the quantity. You will end up with a character variable, and if you want it numeric, just convert it using input() function.

Edit: Inspired by Patrick's possible missing scenario, code has been tweaked.

Regards,

Haikuo

tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

Ok. I am not sure how this will work because I do not have just 1 row that has data. I have 56k rows that have data. So I cannot take the table that I have with 56k rows and run this because I would have to do the data have part 56k times with 30 columns each. That is just too much typing. I have a table that has 30 columns with 56k rows total and want to concatenate that. I can put it in access and do it with no issues, but I need to do this in SAS.

Haikuo
Onyx | Level 15

It will work, regardless you have one row or a billion rows; or you have one numeric variable or 1000 variables. It doesn't hurt to give a try, does it?

Patrick
Opal | Level 21

Think below code should work:

  array ReAdmS {*} ReAdm1-ReAdm30;
  length Readmits $ 500;
  Readmits=cats(of ReAdmS{*});
  Readmits=compress(Readmits,'.'); /* remove "missing" dots */

art297
Opal | Level 21

Editor's note: Thanks to all who gave code samples to accomplish this task.  This answer is the most simplified one that achieves the goal.

 

You can also combine part of the two previous suggestions and use:

 

data have;

input ReAdm1          ReAdm2          ReAdm3            ReAdm4;

cards;

12                    15                       7                                        16

;

 

data want (keep=Readmits);

  set have;

  Readmits=cats(of _numeric_);

run;

Haikuo
Onyx | Level 15

Thanks, Art for "do over" loop. First time to try it out, feels good!

Haikuo

art297
Opal | Level 21

Haikuo: Which is why I like it even if it has been dropped from the documentation.

However, in this particular case, using the 'of' operator is even easier yet!

Haikuo
Onyx | Level 15

Can't agree more. From other's responses, apparently most of the great minds think alike:smileycool:.

Haikuo

Linlin
Lapis Lazuli | Level 10

data have;

input ReAdm1-ReAdm4;

cards;

12 15 7 16

23 5 67 12

;

data want;

  set have;

  ReAdm=cats(of ReAdm1-ReAdm4);

run;

proc print;run;

                             Re      Re      Re      Re

                 Obs    Adm1    Adm2    Adm3    Adm4     ReAdm

 

                   1      12      15       7      16     1215716

                   2      23       5      67      12     2356712

Linlin

PGStats
Opal | Level 21

Assuming your dataset is called have and contains the ReAdm1-ReAdm30 variables:

data want(keep=reAdm);

length reAdm $200;

set have;

reAdm = cats(of reAdm:);

run;

Check the documentation for the cats function. Add the other variables that you want to keep to the keep list.

PG

PG
art297
Opal | Level 21

Slight correction to my previously suggested code.  The cats function will return a number of all of the entries are numbers, unless one first defines the new variable as being character.  Since you are concatinating 30 variables, you will obviously exceed the numeric precision of any computer.

Thus:

data have;

input (x y z) ($) ReAdm1-ReAdm4;

cards;

a  b  c  12 15 7 16

d  e  f  14 15 16 17

;

data want (drop=_numeric_);

  length readmits $300;

  set have;

  Readmits=cats(of _numeric_);

run;

Ksharp
Super User
data have;
input ReAdm1-ReAdm4;
cards;
12 15 . 16
. 5 67 12
;
run;
options missing=' ';
data want;
  set have;
  ReAdm=cats(of ReAdm:);
run; 

Ksharp

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 12 replies
  • 58118 views
  • 7 likes
  • 7 in conversation