"Warning: Variable XXXXX already exists on file YYYYY.ZZZZZ" occurs when you create an output table using the " SELECT * " syntax in the SQL procedure


A warning similar to the following occurs if you create an output table, using the " SELECT * " syntax in PROC SQL, where like-named columns exist in multiple tables listed on the FROM clause:

Warning: Variable XXXXX already exists on file YYYYY.ZZZZZ 

The column that is kept is from the first table that contains that variable.

To work around this problem, individually list the desired columns in a SELECT statement, omitting duplicate column names.

Another workaround is to use of the RENAME= and DROP= data set options, as shown here:

proc sql;
 create table all(drop=tmpid) as
  select * from
   one, two(rename=(id=tmpid))
    where one.id = two.tmpid;
quit;

If you use table aliases, place the RENAME= data set option after the table name and before the table alias. You can omit the DROP= data set option if you want the renamed column in the final output table.