CASE statement

Accepted Solution Solved
Reply
Contributor
Posts: 45
Accepted Solution

CASE statement

I'm trying to get this bit of code to work...

proc sql;

create table one as

select distinct a.prov_id,

case

where d.dgree_catg = 'D' then 'Physician'

where d.dgree_catg = 'M' then 'Physician'

where d.dgree_catg = 'O' then 'Non-Physician'

end as Phys_Non_Phys

from Prov a

     left join dgree_tbl d

          On a.prov_id=d.prov_id

order by a.prov_id

but keep getting errors...

Attachment

Accepted Solutions
Solution
‎05-13-2014 03:46 PM
Trusted Advisor
Posts: 1,204

Re: CASE statement

I am assuming that dgree_catg variable contains values like 'D', 'M'. If this is the case then try putting double quotes in case statement.

case

when d.dgree_catg =  "'D'" then 'Physician'

View solution in original post


All Replies
Contributor
Posts: 45

Re: CASE statement

error message is:

syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, a missing value, (, *, +, -, ALL, ANY, BTRIM, CALCULATED, CASE, INPIT, PUT, SELECT, SOME, SUBSTRING, TRANSLATE, USER.

The symbol is not recognized and will be ignored.

Trusted Advisor
Posts: 1,204

Re: CASE statement

I think case statement requires "when" instead of "where"

Super User
Posts: 17,794

Re: CASE statement

Post the full log with the error. You're missing something in your code that we can't see here.

Regular Contributor
Posts: 217

Re: CASE statement

Case

      when

      when

      else

end as

;

not where.

Contributor
Posts: 45

Re: CASE statement

still got same error when i changed it to where...

Contributor
Posts: 45

Re: CASE statement

posted the the log in the original post.

Solution
‎05-13-2014 03:46 PM
Trusted Advisor
Posts: 1,204

Re: CASE statement

I am assuming that dgree_catg variable contains values like 'D', 'M'. If this is the case then try putting double quotes in case statement.

case

when d.dgree_catg =  "'D'" then 'Physician'

Contributor
Posts: 45

Re: CASE statement

Great thought, yes, the variable is only one of those 3... M, D, or O ... and I added the double quote and it worked!

THANK YOU!!

Just needed double quotes around everything (even the else " " had to have double quotes)

☑ This topic is SOLVED.

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

Discussion stats
  • 8 replies
  • 318 views
  • 0 likes
  • 4 in conversation