concatenating number columns

[tmm]

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

[Haikuo]

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]

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]

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]

Think below code should work:

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

[art297 · Accepted solution]

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]

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

Haikuo

[art297]

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]

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

Haikuo

[Linlin]

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]

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

[art297]

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]

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