Help: ERROR: Duplicate column names have been detected in the above query which requested that CORRE

[buechler66]

I need some help troubleshooting this error. Am I doing something wrong here?

4621      proc sql;
4622      create table QueryData&ZIP5 as
SYMBOLGEN:  Macro variable ZIP5 resolves to 00926
4623      select  *,
4624              "A" as source,
4625              "-" as RuleFlag,
4626              "-" as rule_nm length = 58,
4627              a.actual_dlvry_date as ad_dt,
4628              a.imb_code length = 31,
4629              "-" as rule_order,
4630              a.imb_dlvry_zip_5
4631      from ods_iv_recon_selected_mp as a
4632      where imb_dlvry_zip_5=&ZIP5_QUOTED
SYMBOLGEN:  Macro variable ZIP5_QUOTED resolves to '00926'
4633      union all corresponding
4634      select  *,
4635              "B" as source,
4636              "-" as RuleFlag,
4637              "-" as rule_nm length = 58,
4638              b.actual_dlvry_date as ad_dt,
4639              b.imb_code length = 31,
4640              "-" as rule_order,
4641              b.imb_dlvry_zip_5
4642      from ods_bi_recon_selected_mp as b
4643      where imb_dlvry_zip_5=&ZIP5_QUOTED;
SYMBOLGEN:  Macro variable ZIP5_QUOTED resolves to '00926'
ERROR: Duplicate column names have been detected in the above query which requested that CORRESPONDING column names be
       matched. This situation is ambiguous.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
4644      quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

[Kurt_Bremser · Accepted solution]

Review your input datasets and the columns in there. Since you use select *, all columns from the datasets will be taken, and one or more of them might have the same name as a new column you specified.

[RW9]

Your problem will derive form the use of:

select *

What this means it that you take every variable from all incoming datasets.  Then you are selecting at least one variable a second time:

a.imb_dlvry_zip_5

So the select * will pull in a.imb_dlvry_zip_5 and so will that line in your code, hence you end up with duplicate variables.

Unless there is a specific reason to do so, I would not use select *, it is sloppy coding.  You, the owner of the data and the one who know what they want should specify each and every column you want to find in your output table.  Then, if things change with the table your code does not. 

[BrunoMueller]

hi

Since you are using "select *" the individual column names are not visible

You can add the FEEDBACK option to Proc SQL like so

proc SQL feedback;

quit;

This will then provide the actual select statement as it is processed with all the variable names written out.

this should help with finding duplicate names.

You could also create a table from your individual select statements like this

proc sql feedback;
  create table sample as
  select
    *
    , age as age
  from
    sashelp.class
  ;
quit;

The log will then show which variable names arew duplicates

Bruno