02-05-2016 09:54 AM
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;
02-05-2016 10:33 AM - edited 02-05-2016 10:37 AM
@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;
02-05-2016 10:42 AM
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;
02-05-2016 10:50 AM
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.
02-05-2016 11:01 AM
02-05-2016 11:16 AM
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?