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.
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.
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 |
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.
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.
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=)
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.
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: