topic Re: prevent warning message when merge in proc sql in SAS Programming

prevent warning message when merge in proc sql

Ronein — Mon, 12 Jul 2021 04:18:17 GMT

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;

Re: prevent warning message when merge in proc sql

Kurt_Bremser — Mon, 12 Jul 2021 04:58:29 GMT

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

Re: prevent warning message when merge in proc sql

Patrick — Mon, 12 Jul 2021 05:13:54 GMT

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.

Re: prevent warning message when merge in proc sql

xxformat_com — Mon, 12 Jul 2021 08:34:22 GMT

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;

Re: prevent warning message when merge in proc sql

Patrick — Mon, 12 Jul 2021 08:38:22 GMT

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

Re: prevent warning message when merge in proc sql

xxformat_com — Mon, 12 Jul 2021 11:33:10 GMT

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;

Re: prevent warning message when merge in proc sql

Ksharp — Mon, 12 Jul 2021 12:16:55 GMT

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;