BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
buechler66
Barite | Level 11

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
1 ACCEPTED SOLUTION

Accepted Solutions
3 REPLIES 3
Kurt_Bremser
Super User

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
Diamond | Level 26 RW9
Diamond | Level 26

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
SAS Super FREQ

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

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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