DATA Step, Macro, Functions and more

WHERE CONDITION

Accepted Solution Solved
Reply
Super Contributor
Posts: 1,040
Accepted Solution

WHERE CONDITION

 

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;


Accepted Solutions
Solution
‎10-05-2016 09:27 AM
Super User
Posts: 6,963

Re: WHERE CONDITION

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 6,963

Re: WHERE CONDITION

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 1,040

Re: WHERE CONDITION

[ Edited ]
 
Super User
Posts: 6,963

Re: WHERE CONDITION

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 1,040

Re: WHERE CONDITION

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

 

 

 

Solution
‎10-05-2016 09:27 AM
Super User
Posts: 6,963

Re: WHERE CONDITION

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 1,040

Re: WHERE CONDITION

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

Super User
Posts: 6,963

Re: WHERE CONDITION

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 17,912

Re: WHERE CONDITION

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 

Super User
Super User
Posts: 7,413

Re: WHERE CONDITION

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 403 views
  • 3 likes
  • 4 in conversation