BookmarkSubscribeRSS Feed
SAS_inquisitive
Lapis Lazuli | Level 10

I wonder if this can code have inline view alternative ie. nested query in FROM clause?

PROC SQL;
  CREATE TABLE T1 AS 
  SELECT *
   FROM T2 
     WHERE ID NOT IN (SELECT ID FROM T3) AND
          AGE IN (11,12,13,15);
 QUIT;	       
RUN;
%PUT &SQLOBS;
9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Seems fine to me, whats the problem.  Test data/log output etc.

SAS_inquisitive
Lapis Lazuli | Level 10

@RW9 I want to have all source data from coming FROM clause and use WHERE clause to subset the data after that. If the nested query in WHERE clause can be moved in FROM clause? Like below.

 

PROC SQL;
  CREATE TABLE T1 AS 
  SELECT *
   FROM (SELECT........)
     WHERE AGE IN (11,12,13,15);
 QUIT;	       
RUN;
%PUT &SQLOBS;
Reeza
Super User

Yes it can be moved to the JOIN and conditioning on your WHERE statement.

 

To figure out your conditions I would suggest bringing in both of the ID's, examing the case and determing what you need to get only the rows you want.  Here's a starting point for you:

data class;
set sashelp.class;
where name not in ('Alfred', 'John');
run;

proc sql;
create table want as
select a.*, a.name as ID1, b.name as ID2
from sashelp.class as a
full join class as b 
on a.name=b.name
/*ADD WHERE CLAUSE HERE*/;
quit;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Wrap it the other way round then:

proc sql;
  create table T1 as
  select *
  from   (select *
            from (select........))
  where AGE in (11,12,13,15);
quit;

Note, you don't need the run; part.  If you can provide test data (as a datastep) examples and wht you want the output to look like we can be more accurate.

 

 

 

SAS_inquisitive
Lapis Lazuli | Level 10
@RW9. I thought about that way. Was looking for elegant code. I have really huge and confidential data that's why I did no put it. I should have put some mock data set.

PROC SQL;
CREATE TABLE T1 AS
SELECT *
FROM (
SELECT * FROM T2
WHERE ID NOT IN (SELECT ID FROM T3)
)
WHERE
AGE IN (11,12,13,15);
QUIT;
RUN;
%PUT &SQLOBS;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Not sure what your trying to gain though, it doesn't actually change anything, just a positiiong on the code, won't save you time or processing:

proc sql;
  create table T1 as
  select  *
  from    T2
  where   ID not in (select ID from t3)
    and   age in (11,12,13,15);
quit;

The above is exactly the same, and will run the same?  

SAS_inquisitive
Lapis Lazuli | Level 10

That makes sense.  I  also thought inline view could create virtual table and processing could have been faster.  Not sure this is correct tough.

Reeza
Super User

How is this code different than your original question?

 

Did the code I provided not work?

 

SAS_inquisitive
Lapis Lazuli | Level 10

@Reeza. The code you provided makes sense.  I have not tried your code at production yet. But will give  a shot.  Thanks !

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
  • 9 replies
  • 1637 views
  • 2 likes
  • 3 in conversation