prevent warning message when merge in proc sql

[Ronein]

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;

[Kurt_Bremser]

Do not use asterisks, use explicit lists of variables (for at least one of the two datasets).

[Patrick]

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.

[xxformat_com]

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;

[Patrick]

@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.

[xxformat_com]

True Patrick and I shouldn't try to do to things at the same time.



proc sql;
create table wanted as
select a.*, y
from t1 a
left join t2 b
on a.CustomerID=b.CustomerID;
quit;

[Ksharp · Accepted solution]

Add NOWARN option:


proc sql nowarn;
create table wanted as
select a.*,b.*
from t1 a left join t2 b on a.CustomerID=b.CustomerID;
quit;