Using a macro to automate the catx function

[mbyxcb1]

Hi,

I have multiple variables that I want to join together using the catx function. However, this code is placed within a larger program where the number of start variables is likely to grow over time so I would like to automate it. The current code is:

proc sql;

create table new_table as

select *,

catx(" : ",a1,a2,a3,a4,a5,a6,a7,a8,a9,a10) as list

from old_table

;

quit;

I'd like to automate this to something equivalent to:

proc sql;

create table new_table as

select *,

do i = 1 to &maxcount;

catx(" : ",a&i) as list

end;

from old_table

;

quit;

Where &maxcount is an already created macro variable containing the correct number of varibales present in the dataset. However, everything I've tried doesn't seem to have the desired effect.

Any help would be much appreciated.

[FriedEgg]

You would be better served using a DATA step instead of SQL as you can the use the 'of' operator in CATX() to specify a variable-list or range

data foo;
array a[10] $1 (10*'a');
run;

data bar;
set foo;
list = catx(':',of a:);
run;

/* 
or
list = catx(':',of a1-a&maxcount.);
or
array a[&maxcount.];
list = catx(':',of a[*]);
*/

[PaigeMiller]

UNTESTED CODE, and it must be inside a SAS macro or %do and %if won't work

proc sql;

create table new_table as

select *, catx(" : ",

%do i = 1 %to &maxcount; a&xi %if &i < &maxcount %then %str(,); %end;

)

as list

from old_table

;

quit;

[Reeza]

Similar to FriedEgg's solution
This should work as well

catx(":", of a1-a&maxcount)

[Steelers_In_DC · Accepted solution]

Don't need a macro:

DATA have;
infile cards dsd;
INPUT var1$ var2$ var3$ var4$ var5$;
cards;
one,two,three,four,five
;

%let max = 5;

data want;
set have;
List = catx(":",of var1-var&max);
run;

[mbyxcb1]

Hi all,

Many thanks for all your help guys. I've gone with catx(" : ", of a1-a&maxcount) and it's worked a treat.

Cheers