topic Re: select all columns that start with the same prefix and rename them all for merging in SAS Programming

select all columns that start with the same prefix and rename them all for merging

asasha — Wed, 06 Oct 2021 21:46:15 GMT

I have two datasets with the same column names. I need to left join table B's certain columns to table A. The columns I want are named a_ABC, a_QWERTY, a_XYZ, etc. (i.e. start with "a_"), and there is too many to list one by one. Since table A has those columns as well, I would like to rename them to start with "b_" instead and keep both for comparison.

I was thinking something like:

proc sql;

create table tableC as

select a.*, b.(all that start with a_) as (all starting with b_ instead)

from tableA as a

left join tableB as b
on a.SS = b.SS
and a.ZZ = b.ZZ;

quit;

How can I do this? Thanks.

Re: select all columns that start with the same prefix and rename them all for merging

HB — Wed, 06 Oct 2021 23:11:17 GMT

If data like

a_id	a_filler	amount
a_abc	able	12
a_qwerty	baker	13
a.xyz	charlie	14
abc	delta	15
qwerty	echo	16
xyz	foxtrot	17

and

b_id	b_filler	bmount
b_abc	adam	112
b_qwerty	boy	113
b.xyz	charles	114
abc	david	115
qwerty	edward	116
xyz	frank	117

is sufficient to demonstrate your situation, perhaps you could provide the desired result from your query. I am having a little trouble deciphering what exactly you want.

Re: select all columns that start with the same prefix and rename them all for merging

Reeza — Thu, 07 Oct 2021 00:24:31 GMT

SQL doesn't support short cut lists so this isn't doable in SQL easily. If you provide some more details perhaps we can assist with some general ideas?

One possibility - instead of merge, perhaps concatenate the data and the transpose it using the second data set as the identifier so you can add a prefix at that point?

If the variables have different types this may not work.

data stacked;
set tableA (in=A) tableB (in=B);
if a then prefix="A";
else prefix='B';
run;

proc sort data=long;
by ss zz;
run;

proc transpose data=stacked out=long;
by ss zz;
var a_::
run;

proc transpose data=long out=wide;
by ss zz;
id prefix _name_;
var col1;
run;

Another option that could work - macro code generated by querying the sashelp.vcolumn table.

Re: select all columns that start with the same prefix and rename them all for merging

asasha — Wed, 13 Oct 2021 14:03:07 GMT

@Reeza

@HB

have tableA

ss	zz	a_abc	a_qwerty	a_xyz	abcde
12	13	able	charlie	echo	16
14	15	baker	delta	foxtrot	17

and tableB

ss	zz	a_abc	a_qwerty	a_xyz	abcde
12	13	rock	just	owl	18
14	15	cooperative	modernize	cassette	19

want tableC

ss	zz	a_abc	a_qwerty	a_xyz	abcde	b_abc	b_qwerty	b_xyz
12	13	able	charlie	echo	16	rock	just	owl
14	15	baker	delta	foxtrot	17	cooperative	modernize	cassette

Re: select all columns that start with the same prefix and rename them all for merging

HB — Wed, 13 Oct 2021 15:09:11 GMT

Apparently I really don't understand what is being asked here.

data stuff;
   input ss zz a_abc $ a_qwerty $ a_xyz $ abcde;
datalines;
12 13 able charlie echo 16
14 15 baker delta foxtrot 17
;
run;

data stuff2;
   input ss zz a_abc $ a_qwerty $ a_xyz $ abcde;
datalines;
12 13 rock just owl 18
14 15 cooperative modernize cassette 19
;
run;

proc sql;
	select
	a.ss, a.zz, a.a_abc, a.a_qwerty, a.a_xyz, a.abcde, b.a_abc as b_abc, b.a_qwerty as b_qwerty, b.a_xyz as b_xyz
	from stuff a inner join stuff2 b
	on a.ss = b.ss and a.zz = b.zz;
run;

That code makes this output:

ss	zz	a_abc	a_qwerty	a_xyz	abcde	b_abc	b_qwerty	b_xyz
12	13	able	charlie	echo	16	rock	just	owl
14	15	baker	delta	foxtrot	17	cooperat	moderniz	cassette

but I somehow sense that is not what is truly wanted.

Re: select all columns that start with the same prefix and rename them all for merging

Reeza — Wed, 13 Oct 2021 15:13:09 GMT

OP doesn't want to manually do that rename step, ie b.a_abc as b_abc for the variables.

Re: select all columns that start with the same prefix and rename them all for merging

lpbeaulieu — Thu, 24 Aug 2023 20:21:04 GMT

Before doing the join, you can rename the variables you want for each dataset. Here is a macro to add a suffix to selected variables. You can select variables starting with "a" by giving this as argument when calling the macro: vars=a:

%macro rename_vars(indata=,outdata=,vars=,suffix=);

	proc datasets;
		delete &outdata.;
	quit;	

	proc transpose data=&indata.(obs=0) out=names;
	  var &vars.;
	run;

	proc sql noprint ;
	  select catx('=',_name_, cats(_name_,"&suffix."))
	    into :rename_list separated by ' '
	    from names
	  ; 
	quit;

	data &outdata.;
	set &indata.;
	rename &rename_list.;
	run;

%mend rename_vars;

%rename_vars(indata=,outdata=,vars=a:,suffix=)

Re: select all columns that start with the same prefix and rename them all for merging

mkeintz — Fri, 25 Aug 2023 11:52:46 GMT

Since there is a minor return of attention to this topic, I would include an approach using dictionary.columns to construct the rename text, then use that text as a rename dataset name parameter in the inner join:

data stuff;
   input ss zz a_abc $ a_qwerty $ a_xyz $ abcde;
datalines;
12 13 able charlie echo 16
14 15 baker delta foxtrot 17
run;

data stuff2;
   input ss zz a_abc $ a_qwerty $ a_xyz $ abcde;
datalines;
12 13 rock just owl 18
14 15 cooperative modernize cassette 19
run;

proc sql noprint;
  select catx('=',name,cats('a_',name)) into :rename_list_a separated by ' '
   from dictionary.columns 
   where libname='WORK' and memname='STUFF' and not name in ('ss','zz');

  select catx('=',name,cats('b_',name)) into :rename_list_b separated by ' '
   from dictionary.columns 
   where libname='WORK' and memname='STUFF2' and not name in ('ss','zz');

  create table want as select * from
    stuff (rename=(&rename_list_a)) as a
    inner join
    stuff2 (rename=(&rename_list_b)) as b
    on a.ss=b.ss and a.zz=b.zz ;
quit;

True, this renames ALL vars (except the join vars ss and zz), and the OP asked to rename only the common vars, but it wouldn't need much more code to rename only the common vars.