Prompts erroring in query

[mmagnuson]

I had a case statement,

(case when t1.Date > '01Jan2016' then "NO"

        when t.1L = "N" then "NO"

       when  t.1L = "M" then "NO"

       else "YES" end) As "NewColumn"

I wanted to make a prompt so that when the program was run a person could pick what date they wanted to run the report off of instead of it always being '01Jan2016' or having to go in and changing the value of the date.

I created a prompt 'eligible' and then put it in the code so that it now read this:

case when t1.Date > &eligible then "NO"

        when t.1L = "N" then "NO"

       when  t.1L = "M" then "NO"

       else "YES" end) As "NewColumn"

Now it gives me the erorr: "

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, EQT, GE, GET,"

It seems as though the macro worked because it says:

NOTE: Line generated by the macro variable "ELIGIBLE".

40 01Jan2017

Any help would be greatly appreciated.

[Tom · Accepted solution]

Your original program shouldn't work if your variable is actually a date, but if you want to make the new program look the same then you need to add quotes so that you are generating a valid string literal instead of just the text 01Jan2017.

case
  when t1.Date > "&eligible" then "NO"
  when t.1L = "N" then "NO"
  when  t.1L = "M" then "NO"
  else "YES" 
end As NewColumn

If your DATE variable is really a date and not a character string then you need to write your query using a date literal instead of a string literal.  So add the letter D after the closing quote so SAS knows you mean a date and not just a string literal.

case
  when t1.Date > "&eligible"d then "NO"
  when t.1L = "N" then "NO"
  when  t.1L = "M" then "NO"
  else "YES" 
end As NewColumn

[mmagnuson]

Oh my goodness. I thought I had tried that but I guess I did the single quotes which didn't work

thank you!

[Tom]

---

@mmagnuson wrote:

Oh my goodness. I thought I had tried that but I guess I did the single quotes which didn't work

thank you!

---

SAS does not evaluate macro triggers (& and %) inside of strings that use single quotes as the outer quotes.