BookmarkSubscribeRSS Feed
zoomzoom
Obsidian | Level 7

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.


 


2 REPLIES 2
PGStats
Opal | Level 21

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
Haikuo
Onyx | Level 15

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 412 views
  • 0 likes
  • 3 in conversation