Help using Base SAS procedures

Proper SQL query..

Reply
Regular Contributor
Regular Contributor
Posts: 170

Proper SQL query..

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;
Trusted Advisor
Posts: 2,114

Re: Proper SQL query..

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..
Regular Contributor
Posts: 171

Re: Proper SQL query..

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') .
Regular Contributor
Regular Contributor
Posts: 170

Re: Proper SQL query..

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
Ask a Question
Discussion stats
  • 3 replies
  • 142 views
  • 0 likes
  • 3 in conversation