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
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.
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.
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.
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.