Conditional execution of where condition in proc sql

[zoomzoom]

Hello Members,

Looking for some advice here.

I have three proc sql statements generating three different tables, based on increasing where condition.

Example:

proc sql;

create table ds1 as

select  a.id, a.dt, a.unit

from tab1 a

where a.id not in (select b.id from tab2 b);  

quit;

proc sql;

create table ds2 as

select  a.id, a.dt, a.unit

from tab1 a

where a.id not in (select sai_nbr from tab2)

and a.id not in (select sai_nbr from tab3);

quit;

proc sql;

create table ds3 as

select  a.id, a.dt, a.unit

from tab1 a

where a.id not in (select sai_nbr from tab2)

and a.id not in (select sai_nbr from tab3)

and a.id not in (select sai_nbr from tab4);

quit;

Now, I'd like to create all three data sets in one proc sql or data step. For that, where conditions have to executed conditionally, i.e. when ds1 then one where condition, when ds2 then two where conditions and so on.

I was thinking of creating a counter, and based on increasing value of the counter from 1 to n, the number of where conditions increase. But I'm not sure how to execute increasing where conditions in consecutive iterations.

Compared to data step and proc step, what would be the most efficient and how do I approach the problem? Any advice/idea will be appreciated.

[PGStats]

You cannot create more than one table with a single SQL query. But you might not need more than one table since table ds2 is a subset of table ds1 and table ds3 is a subset of ds2. All three tables could reside togetter as follows:

proc sql;

create table ds1_2_3 as

select 

     id,

     dt,

     unit,

     id not in (select sai_nbr from tab3) as inDs2,

     calculated inDs2 and id not in (select sai_nbr from tab4) as inDs3

from tab1

where id not in (select sai_nbr from tab2);

quit;

Whether this is practical or not depends on the size of the tables and how you intend to use them.

PG

[Haikuo]

Like PG has already pointed out, Proc SQL can't product multiple tables in one pass. However, Hash() can offer a one step approach:

data ds1 ds2 ds3;

  if _n_=1 then do;

     declare hash ds1(dateset:'tab2(keep=id)');

  ds1.definekey('id');

  ds1.definedone();

     declare hash ds2(dateset:'tab3(keep=id)');

  ds2.definekey('id');

  ds2.definedone();

     declare hash ds3(dateset:'tab4(keep=id)');

  ds3.definekey('id');

  ds3.definedone();

  end;

  set tab1;

    if ds1.check() ne 0 then output ds1;

  if ds1.check()*ds2.check() ne 0 then output ds2;

  if ds1.check()*ds2.check()*ds3.check() ne 0 then output ds3;

run;

Not tested, so there maybe typos.

Haikuo