Hello
what is the way that SAS will not show me the warning message
WARNING: Variable CustomerID already exists on file WORK.t2
Data t1;
input CustomerID X;
1 10
2 20
3 30
;
Run;
Data t2;
input CustomerID Y;
1 25
2 40
3 50
;
Run;
proc sql;
create table wanted as
select a.*,b.*
from t1 a
left join t2 b
on a.CustomerID=b.CustomerID;
run;
Do not use asterisks, use explicit lists of variables (for at least one of the two datasets).
If you use the Proc SQL Feedback option....
proc sql feedback;
create table wanted as
select a.*,b.*
from t1 a
left join t2 b
on a.CustomerID=b.CustomerID
;
run;
...you see in the SAS Log select * resolved:
select A.CustomerID, A.X, B.CustomerID, B.Y
from WORK.T1 A left outer join WORK.T2 B on A.CustomerID = B.CustomerID;
...and it's just not conformant SQL to have the same named column twice in the select clause. So SAS is totally right to issue a warning ...and actually SAS is too tolerant imho. If it was me then this would result in an error condition as if ignoring the warning you risk to pick the column from the wrong table.
In regards of the SQL feedback option: If there are many columns then I sometimes use it to generate the resolved select clause in the SAS log, copy/paste this into Notepad++, run some search/replace over it (replace comma with comma and newline), potentially remove the "duplicate" columns and then copy the result back into my SAS code.
proc sql;
create table wanted as
select coalesce(a.customerid,b.customerid) as customerid,
x, y
from t1 a
left join t2 b
on a.CustomerID=b.CustomerID;
quit;
@xxformat_com Given that this is a left join on customerid, a.customerid will always be populated (or both columns will be missing) so no reason for a coalesce() function. It should just be a.customerid in the select clause.
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.