topic WHERE CONDITION in SAS Programming

WHERE CONDITION

robertrao — Wed, 05 Oct 2016 12:49:03 GMT

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;

Re: WHERE CONDITION

Kurt_Bremser — Wed, 05 Oct 2016 12:55:31 GMT

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.

Re: WHERE CONDITION

robertrao — Wed, 05 Oct 2016 13:28:32 GMT

Re: WHERE CONDITION

RW9 — Wed, 05 Oct 2016 12:58:13 GMT

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;

Re: WHERE CONDITION

Kurt_Bremser — Wed, 05 Oct 2016 13:07:15 GMT

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.

Re: WHERE CONDITION

robertrao — Wed, 05 Oct 2016 13:09:13 GMT

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

Re: WHERE CONDITION

Kurt_Bremser — Wed, 05 Oct 2016 13:14:15 GMT

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.

Re: WHERE CONDITION

robertrao — Wed, 05 Oct 2016 13:13:57 GMT

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

Re: WHERE CONDITION

Reeza — Wed, 05 Oct 2016 13:14:56 GMT

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

Re: WHERE CONDITION

Kurt_Bremser — Wed, 05 Oct 2016 13:16:13 GMT

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.