topic Proc sql sub query problem? in SAS Procedures

Proc sql sub query problem?

Mirisage — Tue, 29 Apr 2014 21:08:24 GMT

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);

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

Re: Proc sql sub query problem?

Reeza — Tue, 29 Apr 2014 21:15:23 GMT

Missing the from part, which table are you select b.id's from.

Re: Proc sql sub query problem?

LinusH — Wed, 30 Apr 2014 06:55:24 GMT

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.

Re: Proc sql sub query problem?

sudi — Wed, 30 Apr 2014 07:11:01 GMT

is patid is present in table1 and table2. it would be much more clear to understand your problem if you provide data sets.

Re: Proc sql sub query problem?

RW9 — Wed, 30 Apr 2014 08:31:42 GMT

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.

Re: Proc sql sub query problem?

Dalveer — Wed, 30 Apr 2014 09:36:02 GMT

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.

Re: Proc sql sub query problem?

Mirisage — Thu, 01 May 2014 12:25:28 GMT

Thank you very much everyone.

Hi Dalveer and RW9,

Your examples were very helpful.

Regards

Mirisa