topic Re: Proc Fedsql Join Error in SAS Programming

Proc Fedsql Join Error

phopkinson — Tue, 12 Nov 2024 15:44:01 GMT

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.

Re: Proc Fedsql Join Error

Tom — Tue, 12 Nov 2024 16:49:49 GMT

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.

Re: Proc Fedsql Join Error

LinusH — Wed, 13 Nov 2024 08:54:33 GMT

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?

Re: Proc Fedsql Join Error

Patrick — Thu, 14 Nov 2024 01:12:16 GMT

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;