topic Re: Concatenate all fields ending with a particular word in SAS Programming

Concatenate all fields ending with a particular word

mohdfaisal89 — Thu, 21 Mar 2019 07:59:32 GMT

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

Re: Concatenate all fields ending with a particular word

Kurt_Bremser — Thu, 21 Mar 2019 08:14:39 GMT

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

Re: Concatenate all fields ending with a particular word

PeterClemmensen — Thu, 21 Mar 2019 08:16:04 GMT

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;

Re: Concatenate all fields ending with a particular word

mohdfaisal89 — Thu, 21 Mar 2019 09:57:34 GMT

Thank you ! Worked Great!