topic Re: WARNING MESSAGES in PROC SQL in SAS Programming

WARNING MESSAGES in PROC SQL

chandler — Fri, 02 Jan 2015 20:50:43 GMT

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

Re: WARNING MESSAGES in PROC SQL

stat_sas — Fri, 02 Jan 2015 21:10:56 GMT

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

Re: WARNING MESSAGES in PROC SQL

Reeza — Fri, 02 Jan 2015 22:21:52 GMT

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 ;

Re: WARNING MESSAGES in PROC SQL

mfab — Mon, 05 Jan 2015 14:11:39 GMT

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!