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!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: