BookmarkSubscribeRSS Feed
chandler
Fluorite | Level 6

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

3 REPLIES 3
stat_sas
Ammonite | Level 13

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

Reeza
Super User

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 Smiley Sad


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 ;

mfab
Quartz | Level 8

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 4425 views
  • 0 likes
  • 4 in conversation