WHERE CONDITION

[robertrao]

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;

[Kurt_Bremser]

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.

[Kurt_Bremser]

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.

[robertrao]

Thansk so much. But I specified the Joins at the bottom in WHERE CLAUSES for all the joins.

[Kurt_Bremser · Accepted solution]

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.

[robertrao]

Some tables are inner Joined and some are Left Joined so I specified the Join

[Kurt_Bremser]

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.

[Reeza]

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 

[RW9]

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;