convert set statements into sql

[chithra]

Hi ,

I need to convert the below statement into procsql;

data work.W10Z35LY;
set work.W10Y7DVC
work.W10VUM5G;
keep A,B,C;
run;

Could you please help?

Thanks,

Chithra

[Jagadishkatam]

You can try the below code

proc sql;
create table work.W10Z35LY as select A,B,C from work.W10Y7DVC
union all
select A,B,C from work.W10VUM5G;
quit;

[chithra]

ok, thanks..will try

apart from union,

is it there ahything similar in sql joins?

i ned something from sql join

[ed_sas_member]

Hi @chithra 

You data step shows a concatenation of two datasets, not a merge.

What should be the join key?

Please post some sample data in datelines.

Best,

[chithra]

Hi all,

Union all works here.

But i need the final outpout to be stored in oracle using proc sql.

And it is slow here. compared to data step.

Thanks,

Chithra

[Kurt_Bremser]

So it took a thread with 6 replies for you to come up with the very crucial information that you work with a remote DBMS.

Please be more complete in supplying the necessary information when you post questions.

If all your tables reside on Oracle, then you should execute the SQL UNION in explicit passthrough.

If you need to append data from SAS to existing Oracle tables, use proc append, or upload the data to Oracle and do the UNION there.

If all your source tables reside in SAS, and only the end result needs to be in Oracle, use the DATA step.

The network transfer rate between the SAS and Oracle servers will be the deciding factor when it comes to performance if you need to move data between the two.

Before we go on, you need to tell us exactly what you want to do, and supply table locations and sizes (in terms of observation numbers).

[Kurt_Bremser]

---

@chithra wrote:

ok, thanks..will try

apart from union,

is it there ahything similar in sql joins?

i ned something from sql join

---

A concatenation (that's what your data step code does) is done with a UNION in SQL, not with a join, period. Since UNION code takes a lot more typing than a SET with multiple datasets, the data step is the tool of choice for concatenations.

There are also considerations to be done if not all columns are present in all datasets. Once again, the data step is much simpler here.

[LinusH]

Can you please describe the requirement?

It's very specific that you need a SQL join...?

[s_lassen]

As the output is a simple concatenation, another possibility is to use an INSERT for the second input table, instead of a UNION:

proc sql;
  create table work.W10Z35LY as select a,b,c from work.W10Y7DVC;
  insert into work.W10Z35LY select a,b,c from work.W10VUM5G;
quit;

If both your inputs and your output are in Oracle, you may improve performance by using pass-through SQL, e.g.:

proc sql;
  connect to Oracle <connect options>;
  execute by oracle(
    create table W10Z35LY as select a,b,c from W10Y7DVC
    );
  execute by oracle(
    insert into W10Z35LY select a,b,c from W10VUM5G
    );
quit;