Where+Rename+IN options in merge in proc sql

[Ronein]

Hello

I am running merge in proc sql.

I have Where+Rename+IN  statements .

I get an error so I think that the order of  them is not correct

What is the correct order of   Where+Rename+IN options in merge?

What is the logic of the correct order? IS there order of actions?

PROC SQL;
	create table output as
	select 	a.*,b.*   
	from AllModels(where=(_TYPE_=3)in=a ),
         AllModels(where=(_TYPE_=0)  rename=(Obligo=TotalObligo )in=b) 
;
QUIT;

[Kurt_Bremser]

in = dataset options are only valid in data steps.

What you are looking for in SQL are table aliases:

proc sql;
create table output as
select
 a.*,b.*   
from
  AllModels (
    where=(_TYPE_=3)
  ) as a,
  AllModels (
    where=(_TYPE_=0)
    rename=(Obligo=TotalObligo)
  ) as b 
;
quit;

Note that this SQL willl create a cartesian product (the target dataset will have (nobs of AllModels) ** 2 observations).

It will also create a NOTE or WARNING for columns already present.

Could it be you are looking for a vertical stacking of datasets? That would best be achieved with a simple data step.

[RW9]

Please differentiate what is SAS syntax and what is SQL syntax.  Whilst you can use some dataset options in SQL, it really isn't recommended.  

proc sql; 
  create table output as
  select a.*,
         b.obligo as totalobligo   
  from   (select * from allmodels where _type_=3) a,
         (select * from allmodels where _type_=0) b;
quit;

Do note a few things also, select * from multiple tables will cause you issues.  The principal is that you should know your data, know what it contains, and what you want in the output.