SAS Beginer- issues with Join statemet in proc sql

Deepak13 — Sun, 28 Jul 2019 13:07:31 GMT

Hi,

Even after using "on" function error pops up. Please clarify.

data Marks;
input Roll_ID Math Physics English Chemistry;
cards;
001 85 48 75 96
002 98 76 58 39
003 74 56 35 82
004 78 85 79 63
005 48 97 36 85
;
run;
proc print data=marks;
run;

data Marks1;
input Roll_ID Biology Zoology;
cards;
001 25.36 36.21
002 96.25 87.25
003 82.15 63.24
007 98.69 96.78
006 87.25 54.36
;
run;
proc print data=Marks1;
run;

proc sql;
create table Student_marklist
as select *
from marks as A
inner join
from marks1 as B 
on A. roll_ID = B. roll_ID;
quit;

proc sql;
create table Student_marklist
as select *
from marks as A
full join
from marks1 as B 
on A. roll_ID = B. roll_ID;
quit;

proc sql;
create table Student_marklist
as select *
from marks as A
left join
from marks1 as B 
on A. roll_ID = B. roll_ID;
quit;

proc sql;
create table Student_marklist
as select *
from marks as A
right join
from marks1 as B 
on A. roll_ID = B. roll_ID;
quit;


LOG:
OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 72         
 73         proc sql;
 74         create table Student_marklist
 75         as select *
 76         from marks as A
 77         inner join
 78         from marks1 as B
                        __
                        73
                        201
 ERROR 73-322: Expecting an ON.
 
 ERROR 201-322: The option is not recognized and will be ignored.
 
 79         on A. roll_ID = B. roll_ID;
 NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
 80         quit;
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 81         
 
 
 82         proc sql;
 83         create table Student_marklist
 84         as select *
 85         from marks as A
 86         full join
 87         from marks1 as B
                        __
                        73
                        201
 ERROR 73-322: Expecting an ON.
 
 ERROR 201-322: The option is not recognized and will be ignored.
 
 88         on A. roll_ID = B. roll_ID;
 NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
 89         quit;
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 90         
 
 
 91         proc sql;
 92         create table Student_marklist
 93         as select *
 94         from marks as A
 95         left join
 96         from marks1 as B
                        __
                        73
                        201
 ERROR 73-322: Expecting an ON.
 
 ERROR 201-322: The option is not recognized and will be ignored.
 
 97         on A. roll_ID = B. roll_ID;
 NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
 98         quit;
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 99         
 
 
 100        proc sql;
 101        create table Student_marklist
 102        as select *
 103        from marks as A
 104        right join
 105        from marks1 as B
                        __
                        73
                        201
 ERROR 73-322: Expecting an ON.
 
 ERROR 201-322: The option is not recognized and will be ignored.
 
 106        on A. roll_ID = B. roll_ID;
 NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
 107        quit;
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 108        
 109        
 110        OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

Re: SAS Beginer- issues with Join statemet in proc sql

Kurt_Bremser — Sun, 28 Jul 2019 13:17:00 GMT

Remove the surplus "froms". Join syntax is

from x
inner join y
on ......
left join z
on .....

Re: SAS Beginer- issues with Join statemet in proc sql

Kurt_Bremser — Sun, 28 Jul 2019 13:20:11 GMT

EXTREMELY IMPORTANT literature found here:

SQL Procedure User's Guide

There is a reason why Maxim 1 (Read the Documentation) is number 1.

Re: SAS Beginer- issues with Join statemet in proc sql

cmurugesan — Mon, 29 Jul 2019 22:00:16 GMT

like @Kurt_Bremser said, there is duplicate "FROM" clause in the join.

Re: SAS Beginer- issues with Join statemet in proc sql

Deepak13 — Wed, 31 Jul 2019 03:14:45 GMT

Many thanks @Kurt_Bremser for sharing valuable literature. I think this would help me in setting up my career path in SAS.

topic Re: SAS Beginer- issues with Join statemet in proc sql in New SAS User

SAS Beginer- issues with Join statemet in proc sql

Re: SAS Beginer- issues with Join statemet in proc sql

Re: SAS Beginer- issues with Join statemet in proc sql

Re: SAS Beginer- issues with Join statemet in proc sql

Re: SAS Beginer- issues with Join statemet in proc sql