DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Regular Contributor
Posts: 212
Accepted Solution

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

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

Accepted Solutions
Solution
‎08-16-2016 11:31 AM
Super User
Posts: 7,832

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

Posted in reply to buechler66

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎08-16-2016 11:31 AM
Super User
Posts: 7,832

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

Posted in reply to buechler66

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,977

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

Posted in reply to buechler66

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. 

SAS Super FREQ
Posts: 708

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

[ Edited ]
Posted in reply to buechler66

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 583 views
  • 3 likes
  • 4 in conversation