Help using Base SAS procedures

Proc sql sub query problem?

Accepted Solution Solved
Reply
Super Contributor
Posts: 338
Accepted Solution

Proc sql sub query problem?

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


Accepted Solutions
Solution
‎04-30-2014 05:36 AM
Occasional Contributor
Posts: 10

Re: Proc sql sub query problem?

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.

View solution in original post


All Replies
Super User
Posts: 17,784

Re: Proc sql sub query problem?

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

Super User
Posts: 5,256

Re: Proc sql sub query problem?

Your program seems to change when it appears in the log...Smiley Wink

Seriously, what are you trying to achieve? I can't see the logic even if I could put your query syntactically correct.

Data never sleeps
Occasional Contributor
Posts: 5

Re: Proc sql sub query problem?

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

Super User
Super User
Posts: 7,397

Re: Proc sql sub query problem?

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.

Solution
‎04-30-2014 05:36 AM
Occasional Contributor
Posts: 10

Re: Proc sql sub query problem?

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.

Super Contributor
Posts: 338

Re: Proc sql sub query problem?

Thank you very much everyone.

Hi Dalveer and RW9,

Your examples were very helpful.

Regards

Mirisa

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 842 views
  • 3 likes
  • 6 in conversation