Help using Base SAS procedures

concatenating number columns

Reply
Regular Contributor
Regular Contributor
Posts: 238

concatenating number columns

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

Respected Advisor
Posts: 3,124

Re: concatenating number columns

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

Regular Contributor
Regular Contributor
Posts: 238

concatenating number columns

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.

Respected Advisor
Posts: 3,124

Re: concatenating number columns

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?

Respected Advisor
Posts: 3,908

concatenating number columns

Think below code should work:

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

PROC Star
Posts: 7,366

concatenating number columns

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;

Respected Advisor
Posts: 3,124

Re: concatenating number columns

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

Haikuo

PROC Star
Posts: 7,366

Re: concatenating number columns

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!

Respected Advisor
Posts: 3,124

Re: concatenating number columns

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

Haikuo

Super Contributor
Posts: 1,636

concatenating number columns

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

Respected Advisor
Posts: 4,663

concatenating number columns

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

data want(keep=reAdm);

length reAdm $200;

set have;

reAdm = cats(of reAdmSmiley Happy;

run;

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

PG

PG
PROC Star
Posts: 7,366

Re: concatenating number columns

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;

Super User
Posts: 9,691

Re: concatenating number columns

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

Ask a Question
Discussion stats
  • 12 replies
  • 22578 views
  • 7 likes
  • 7 in conversation