concatenate multiple columns

[mhouse]

Hello,

I have a data set that I'd like to have a few column concatenated into one colum. The data set and variables are like this:

(10 variables)

department item cat1 cat2 cat3 cat4 cat5 nt1 nt2 nt3
aaa        v    1a   2a   3a   4a   5a   1a  2a  
bbb        w    1b   2b   3b             1b  2b  3b
ccc        x    1c   2c   3c   4c        1c  2c  3c
ddd        y    1d   2d   3d   4d   5d   1d  2d  3d

The end table should look like this (3 variables):

department item newvar
aaa        v    1a, 2a, 3a, 4a, 5a, 1a, 2a
bbb        w    1b, 2b, 3b, 1b, 2b, 3b
ccc        x    1c, 2c, 3c, 4c, 1c, 2c, 3c
ddd        y    1d, 2d, 3d, 4d, 5d, 1d, 2d, 3d

These cat and nt variables are obtained by joining two tables (catTable and ntTable) so we have the option of working with one table at a time and concatenating twice, but the number of cat and nt variables can vary at any time so concatenating needs to be dynamic.

I thought about using this SAS option to create a macro but didn't know how to make the macro variable work. Thanks in advance for your help.

dsid1 = open("catTable");
dsid2 = open("ntTable");
&num_cat = attrn(dsid1,"NVARS")-2;
&num_nt = attrn(dsid2,"NVARS")-2;

[Reeza]

Declare an array that contains the max amount of variables then use CATX. You appear to be filling in missing values? You'll have to account for that logic. 

array vars(*) cat1-cat&num_cat nt1-nt&num_nt;

new_var = catx(",", of vars(*));

[FreelanceReinh · Accepted solution]

Hello @mhouse,

If the "cat and nt variables" have these numbered names, you could try the second of these two steps:

data have;
input (department item cat1 cat2 cat3 cat4 cat5 nt1 nt2 nt3) (:$3.);
cards;
aaa        v    1a   2a   3a   4a   5a   1a  2a  .
bbb        w    1b   2b   3b    .    .   1b  2b  3b
ccc        x    1c   2c   3c   4c    .   1c  2c  3c
ddd        y    1d   2d   3d   4d   5d   1d  2d  3d
;

data want;
set have;
length newvar $100; /* Please adapt length as appropriate. */
newvar=catx(', ', of cat:, of nt:);
drop cat: nt:;
run;

[mhouse]

Thank you @Reeza and @FreelanceReinh! I didn't know how to make the macro variables work without writing a full macro but the second solution worked for my purpose.

Thanks again,

mhouse