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;
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;
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(*));
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;
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.