topic Re: Where+Rename+IN options in merge in proc sql in SAS Programming

Where+Rename+IN options in merge in proc sql

Ronein — Wed, 29 Aug 2018 06:34:51 GMT

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;

Re: Where+Rename+IN options in merge in proc sql

Kurt_Bremser — Wed, 29 Aug 2018 07:06:40 GMT

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.

Re: Where+Rename+IN options in merge in proc sql

RW9 — Wed, 29 Aug 2018 08:25:26 GMT

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.