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