data want;
set DMS.Dms1;
length newvar $100; /* Please adapt length as appropriate. */
newvar=catt(', ', of _A:);
run;
Please Help !
I want to concatenate all the columns ending with _A in a table. I have more then 60 columns ending with _A in a table of 90 columns
data testdata;
input (ID FirstVar SecondVar ThirdVar One_A Two_A Three_A)(:$10.);
datalines;
1 1 2 3 1 2 3
2 4 5 6 4 5 6
3 7 8 9 7 8 9
4 1 2 3 1 2 3
5 4 5 6 4 5 6
;
%let suffix=A;
proc sql noprint;
select name into :vars separated by ','
from dictionary.columns
where upcase(libname)=upcase('work')
and upcase(memname)=upcase('testdata')
and upcase(substr(name,length(name)-(length("&suffix")-1),length("&suffix")))=upcase("&suffix");
quit;
%put &vars;
data want;
set testdata;
newvar=cats(&vars);
run;
You will need to retrieve the variable names from dictionary.columns:
data have;
input col1 $ col1_a $ col2_a $;
cards;
x y z
;
run;
proc sql noprint;
select trim(name) into :vars separated by ','
from dictionary.columns
where libname = 'WORK' and memname = 'HAVE'
and upcase(substr(name,length(name)-1,2)) = '_A'
;
quit;
data want;
set have;
length newvar $100;
newvar = catx(',',&vars);
run;
proc print data=want noobs;
run;
Result:
col1 col1_a col2_a newvar x y z y,z
data testdata;
input (ID FirstVar SecondVar ThirdVar One_A Two_A Three_A)(:$10.);
datalines;
1 1 2 3 1 2 3
2 4 5 6 4 5 6
3 7 8 9 7 8 9
4 1 2 3 1 2 3
5 4 5 6 4 5 6
;
%let suffix=A;
proc sql noprint;
select name into :vars separated by ','
from dictionary.columns
where upcase(libname)=upcase('work')
and upcase(memname)=upcase('testdata')
and upcase(substr(name,length(name)-(length("&suffix")-1),length("&suffix")))=upcase("&suffix");
quit;
%put &vars;
data want;
set testdata;
newvar=cats(&vars);
run;
Thank you ! Worked Great!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.