Proc Fedsql Join Error

[phopkinson]

Hi,

I am using the proc fedsql to join two datasets - however they have multiple columns with the same name which isn't allowed is there an option to accept that there are these two columns and treat them as identical because it is just a bunch of ID and Product columns. Thanks in advance.

[Tom]

I do not think so. Welcome to the verbose syntax of SQL. 

You will just need to list the variables ("columns") that do want selected.

[LinusH]

There is no general option that manges this in (fed)SQL.

How to handle depends on your situation.

Do you use  an inner or an outer join?

Are the same name columns part of the join criteria?

If not, do you expect them to have the same values in both data sets?

[Patrick]

You can't create tables with duplicate variable names and though must ensure that they are unique in your Select statement. That's true for any SQL flavour. Proc SQL would just throw a warning, and database SQL I know would throw an error same as Proc Fedsql.

I'm not a big fan of * in the Select statement but below an example for a minimum typing approach.

data work.class1(drop=sex) work.class2;
  set sashelp.class;
run;
proc datasets lib=work nolist nowarn;
  delete want;
quit;
proc FEDsql;
  create table want as
  select 
    t1.*
    , t2.sex
  from work.class1 t1 inner join work.class2 t2
  on t1.name=t2.name
  ;
quit;