Conditional execution of where condition in proc sql

Reply
Contributor
Posts: 36

Conditional execution of where condition in proc sql

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.


 


Respected Advisor
Posts: 4,641

Re: Conditional execution of where condition in proc sql

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

PG
Respected Advisor
Posts: 3,124

Re: Conditional execution of where condition in proc sql

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

Ask a Question
Discussion stats
  • 2 replies
  • 154 views
  • 0 likes
  • 3 in conversation