Hi SAS Forum,
I was trying to understand how does the proc sql sub query function (I never used sub querying).
Could you please let me know what is the problem in the code below.
proc sql;
create table anyname as
select a.*
,b.*
from Table1 a
,Table2 b
where a.acctid not IN (select b.acctid);
quit;
Error messege comes like below:
15 proc sql;
16 create table dunga as select
17 a.*
18 ,b.*
19 from a1 a
20 ,a2 b
21 where a.patid not IN (select b.patid);
_
22
76
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?,
AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE, LEN, LENGTH,
LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.
ERROR 76-322: Syntax error, statement will be ignored.
Thanks, Mirisa
hi,
in your sub-query you have not mention the table name from where you are taking the variable name, means if you write 'from' in sub-query
then it work perfectly.
proc sql;
create table newtable as
select a.*,b.*
from Table1 a,Table2 b
where a.acctid not in (select b.acctid from table2 b);
quit;
answer:- the above code make Cartesian product and give the result as:
if you go with the following code:
proc sql;
create table anewtable2 as
select a.*
from Table1 a
where a.acctid not in (select b.acctid from table2 b);
quit;
then the output is:-
Note:- do-not worry about the values,i have just assumed.
Missing the from part, which table are you select b.id's from.
Your program seems to change when it appears in the log...
Seriously, what are you trying to achieve? I can't see the logic even if I could put your query syntactically correct.
is patid is present in table1 and table2. it would be much more clear to understand your problem if you provide data sets.
I assume you want a table where the acctid only appears in table1 not table2?
proc sql;
create table anyname as
select *
from Table1
where acctid not in (select distinct acctid from table2);
quit;
Though, why do you select all table 2's variables as the point of the not in is to select data where it doesn't exist in table2.
hi,
in your sub-query you have not mention the table name from where you are taking the variable name, means if you write 'from' in sub-query
then it work perfectly.
proc sql;
create table newtable as
select a.*,b.*
from Table1 a,Table2 b
where a.acctid not in (select b.acctid from table2 b);
quit;
answer:- the above code make Cartesian product and give the result as:
if you go with the following code:
proc sql;
create table anewtable2 as
select a.*
from Table1 a
where a.acctid not in (select b.acctid from table2 b);
quit;
then the output is:-
Note:- do-not worry about the values,i have just assumed.
Thank you very much everyone.
Hi Dalveer and RW9,
Your examples were very helpful.
Regards
Mirisa
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.