topic Proper SQL query.. in SAS Procedures

Proper SQL query..

_LB — Tue, 08 Mar 2011 00:02:15 GMT

Hello all-
I have a Proc SQL that goes like this (truncated to the problem part.)

To explain-the format $Special gives either a 'BMT' or 'ONC' designation.
What I am attempting to do is make a case in that when it does not match those two parameters it goes to another table and grabs the designaton from there. I have tried difference variances such as call the format and then try to override when blank, but that doesn't seem to work either..
Any advice would be helpful.

Thank you.

Lawrence

proc sql;
create table L7 as Case when PUT(A.Attending_Physician, $Special.) ne ('BMT'||'ONC')
then C.Svce else PUT(A.Attending_Physician, $Special.) end as SVC
FROM ACUTE7L as A LEFT JOIN GOLDSBY as C on A.VISIT=C.VISIT;
QUIT;

Re: Proper SQL query..

Doc_Duke — Tue, 08 Mar 2011 02:54:23 GMT

I think that you have to do this with two queries.

The CASE statement is a mixture of SQL and DATA step logic (CASE clauses are OK, THEN and ELSE are not). Lastly, the PUT (..) NE ('BMT'||'ONC') won't work because you are both mixed syntax and are using a concatenation operator.

Create a table for the BMT and ONC folk and then one for the others. Finally combine the two using the UNION operator in SQL..

Re: Proper SQL query..

polingjw — Tue, 08 Mar 2011 13:43:15 GMT

In your query, the case expression needs to be part of a select clause. Try adding the word SELECT before the word CASE in your proc sql step. I don't understand what PUT(A.Attending_Physician, $Special.) ne ('BMT'||'ONC') is suppose to do. Based on your description, I think that you might want to use PUT(A.Attending_Physician, $Special.) not in ('BMT' 'ONC') .

Re: Proper SQL query..

_LB — Tue, 08 Mar 2011 16:00:48 GMT

Thanks for the help, Polingjw's query worked best.
The PUT(A.Attending_Physician, $Special.) simply assigns a format that I created. Just my SQL could use some work.

Thank you.

Lawrence