BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mirisage
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Dalveer
Calcite | Level 5

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

6 REPLIES 6
Reeza
Super User

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

LinusH
Tourmaline | Level 20

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
sudi
Calcite | Level 5

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Dalveer
Calcite | Level 5

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.

Mirisage
Obsidian | Level 7

Thank you very much everyone.

Hi Dalveer and RW9,

Your examples were very helpful.

Regards

Mirisa

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!

What is Bayesian Analysis?

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.

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
  • 6 replies
  • 4013 views
  • 3 likes
  • 6 in conversation