topic Re: Using a macro to automate the catx function in SAS Programming

Using a macro to automate the catx function

mbyxcb1 — Fri, 18 Sep 2015 15:51:15 GMT

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.

Re: Using a macro to automate the catx function

FriedEgg — Fri, 18 Sep 2015 21:52:48 GMT

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[*]);
*/

Re: Using a macro to automate the catx function

PaigeMiller — Fri, 18 Sep 2015 16:07:59 GMT

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;

Re: Using a macro to automate the catx function

Reeza — Fri, 18 Sep 2015 17:00:45 GMT

Similar to FriedEgg's solution
This should work as well

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

Re: Using a macro to automate the catx function

Steelers_In_DC — Fri, 18 Sep 2015 18:58:59 GMT

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;

Re: Using a macro to automate the catx function

mbyxcb1 — Mon, 21 Sep 2015 10:48:48 GMT

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