BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
robertrao
Quartz | Level 8

 

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

9 REPLIES 9
Kurt_Bremser
Super User

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.

robertrao
Quartz | Level 8
 
Kurt_Bremser
Super User

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
Quartz | Level 8

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

 

 

 

Kurt_Bremser
Super User

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
Quartz | Level 8

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

Kurt_Bremser
Super User

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
Super User

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
Diamond | Level 26 RW9
Diamond | Level 26

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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