topic Re: convert set statements into sql in SAS Programming

convert set statements into sql

chithra — Wed, 13 May 2020 08:05:41 GMT

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

Re: convert set statements into sql

Jagadishkatam — Wed, 13 May 2020 08:10:02 GMT

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;

Re: convert set statements into sql

chithra — Wed, 13 May 2020 08:11:18 GMT

ok, thanks..will try

apart from union,

is it there ahything similar in sql joins?

i ned something from sql join

Re: convert set statements into sql

ed_sas_member — Wed, 13 May 2020 08:13:55 GMT

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,

Re: convert set statements into sql

Kurt_Bremser — Wed, 13 May 2020 08:46:54 GMT

@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.

Re: convert set statements into sql

LinusH — Wed, 13 May 2020 10:50:11 GMT

Can you please describe the requirement?

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

Re: convert set statements into sql

chithra — Thu, 14 May 2020 06:56:28 GMT

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

Re: convert set statements into sql

Kurt_Bremser — Thu, 14 May 2020 07:10:05 GMT

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).

Re: convert set statements into sql

s_lassen — Thu, 14 May 2020 11:01:05 GMT

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;