Hello,
I am using multiple conditions in WHERE option like shown below.
I am getting an error here. Could someone help me if I am using the syntax/braces properly?
Thanks
proc sql;
create table meds as
select * from
medication_table ( keep=medication_id display_name
where=( medication_ID in(1234,5678)
or index(upcase(display_name),'MEDICINE') > 0 )) as med
;
quit;
run;
Then you must not use a join. Correct code is
select a.x, b.y, c.z
from data1 a, data2 b, data3 c
where a.key1 = b.key1 and b.key2 = c.key2
;
Be aware that such a step will have **VERY** bad performance when large datasets are involved; in such cases, it is usually better to do the join in a sequence of sort and data steps.
Could you please post the log with the code and error message? I did not get an error when running this (with other variable names and values) against sashelp.class:
proc sql;
create table meds as
select * from
sashelp.class (
keep=age name
where=(
age in(19,18)
or index(upcase(name),'JANE') > 0
)
)
;
quit;
Note that I omitted the "as med", as that makes no sense at this place.
Well, the log is very specific here:
@robertrao wrote:
250 left join clrt.CL_PRL_SS(keep=protocol_id PROTOCOL_NAME) as cl_prl_ss,
-
73
76
ERROR 73-322: Expecting an ON.
ERROR 76-322: Syntax error, statement will be ignored.
251
You specified a JOIN without the required ON condition. SAS recognizes this as soon as it encounters the comma that ends the join clause.
Thansk so much. But I specified the Joins at the bottom in WHERE CLAUSES for all the joins.
Then you must not use a join. Correct code is
select a.x, b.y, c.z
from data1 a, data2 b, data3 c
where a.key1 = b.key1 and b.key2 = c.key2
;
Be aware that such a step will have **VERY** bad performance when large datasets are involved; in such cases, it is usually better to do the join in a sequence of sort and data steps.
Some tables are inner Joined and some are Left Joined so I specified the Join
Whatever you do, it must be syntactically correct. The match condition for a JOIN must be provided in the ON and not in the WHERE clause. Period.
Then you can't specify the join type, ie left join.
If you specify join type you need to specify the join condition with an ON
You are mixing SAS and SQL there. Would not recommend doing it like that, use one or the other:
proc sql; create table meds as select medication_id, display_name from medication_table where medication_ID in (1234,5678) or index(upcase(display_name),'MEDICINE') > 0; quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.