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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.