topic Re: concatenating number columns in SAS Procedures

concatenating number columns

tmm — Wed, 22 Feb 2012 22:44:15 GMT

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

Re: concatenating number columns

Haikuo — Wed, 22 Feb 2012 22:57:22 GMT

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

concatenating number columns

Patrick — Wed, 22 Feb 2012 22:58:37 GMT

Think below code should work:

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

concatenating number columns

tmm — Wed, 22 Feb 2012 23:00:14 GMT

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.

concatenating number columns

art297 — Wed, 30 Aug 2017 14:44:12 GMT

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;

concatenating number columns

Linlin — Wed, 22 Feb 2012 23:04:37 GMT

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

Re: concatenating number columns

Haikuo — Wed, 22 Feb 2012 23:05:21 GMT

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?

concatenating number columns

PGStats — Wed, 22 Feb 2012 23:05:41 GMT

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.

Re: concatenating number columns

Haikuo — Wed, 22 Feb 2012 23:07:01 GMT

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

Haikuo

Re: concatenating number columns

art297 — Wed, 22 Feb 2012 23:10:43 GMT

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!

Re: concatenating number columns

Haikuo — Wed, 22 Feb 2012 23:14:46 GMT

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

Haikuo

Re: concatenating number columns

art297 — Wed, 22 Feb 2012 23:21:58 GMT

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;

Re: concatenating number columns

Ksharp — Thu, 23 Feb 2012 06:11:03 GMT

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