DATA Step, Macro, Functions and more

Using a macro to automate the catx function

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Using a macro to automate the catx function

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.


Accepted Solutions
Solution
‎09-25-2015 06:23 AM
Valued Guide
Posts: 858

Re: Using a macro to automate the catx function

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;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,300

Re: Using a macro to automate the catx function

[ Edited ]

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[*]);
*/
Trusted Advisor
Posts: 1,621

Re: Using a macro to automate the catx function

[ Edited ]

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;

Super User
Posts: 17,868

Re: Using a macro to automate the catx function

Similar to FriedEgg's solution
This should work as well

catx(":", of a1-a&maxcount)
Solution
‎09-25-2015 06:23 AM
Valued Guide
Posts: 858

Re: Using a macro to automate the catx function

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;

New Contributor
Posts: 2

Re: Using a macro to automate the catx function

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 227 views
  • 1 like
  • 5 in conversation