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;
Remove the surplus "froms". Join syntax is
from x
inner join y
on ......
left join z
on .....
Remove the surplus "froms". Join syntax is
from x
inner join y
on ......
left join z
on .....
PS
EXTREMELY IMPORTANT literature found here:
There is a reason why Maxim 1 (Read the Documentation) is number 1.
Many thanks @Kurt_Bremser for sharing valuable literature. I think this would help me in setting up my career path in SAS.
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.