Need help correcting code to eliminate these WARNING MESSAGES.
proc sql;
create table work.obgtick4 as
select distinct *
from work.obgtick3 A
inner join daily_obln_table b
on a.bank = b.bank and a.obligor = b.obligor and a.obligation =
! b.obligation
ORDER BY SORT_LOB, DIVISION_NAME, RM_LOB, ASSN, OFFICER_NAME, CONT,
BANK, OBLIGOR, OBLIGATION ;
WARNING: Column named BANK is duplicated in a select expression (or a view).
Explicit references to it will be to the first one.
WARNING: Column named OBLIGOR is duplicated in a select expression (or a
view). Explicit references to it will be to the first one.
WARNING: Column named OBLIGATION is duplicated in a select expression (or a
view). Explicit references to it will be to the first one.
WARNING: Variable bank already exists on file WORK.OBGTICK4.
WARNING: Variable obligor already exists on file WORK.OBGTICK4.
WARNING: Variable obligation already exists on file WORK.OBGTICK4.
NOTE: Table WORK.OBGTICK4 created, with 2160 rows and 338 columns.
quit ;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.81 seconds
cpu time 0.42 seconds
This is because same variable names are being referred more than once in select statement. If some of the variables are appearing in multiple tables just use table alias to refer them in select statement. Something like this
proc sql;
select A.bank, b.obligor.....
This occurs because you have the variable BANK in both tables but your select is selecting all variables from both tables, so you now have two variables named BANK, even though you've specified they need to be equal in the query. SAS/SQL isn't smart enough to distinguish this
Add the keyword FEEDBACK to your initial PROC SQL statement. Check the log, it will have the query fully expanded for you, including the alias selection. Then you can remove the duplicates as specified in the warning messages.
proc sql FEEDBACK;
create table work.obgtick4 as
select distinct *
from work.obgtick3 A
inner join daily_obln_table b
on a.bank = b.bank and a.obligor = b.obligor and a.obligation =
! b.obligation
ORDER BY SORT_LOB, DIVISION_NAME, RM_LOB, ASSN, OFFICER_NAME, CONT,
BANK, OBLIGOR, OBLIGATION ;
You will receive a message, because you have variables with the same name in both tables and you should tell the system within your SQL, which variable should be selected.
So "select a.bank, ..." instead of "select bank, ..." or "select *..." is more clean.
However the statement will work more less flawless and SAS will take the column (variable) from the table that you stated first in your "from" statement.
The deal with your statement is that you have an "order by" within your SQL. You also need to tell SAS by which column it should sort the output data.
That is exaclty the Message you receive by now:
WARNING: Column named BANK is duplicated in a select expression (or a view).
Explicit references to it will be to the first one.
So "...order by a.bank..." is more clean than "...order by bank ...".
The log tells you that "explicit references" will refer to the first one. That means from the first table in the "From"-statement.
"Order By bank" is an explicit reference and you should really explicitly declare which column is meant.
"select *" is not an explicit reference, however the code is cleaner if you state which columns you want.
Hope this helps!
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.