Desktop productivity for business analysts and programmers

Prompts erroring in query

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

Prompts erroring in query

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.


Accepted Solutions
Solution
‎09-13-2017 12:34 PM
Super User
Super User
Posts: 7,162

Re: Prompts erroring in query

[ Edited ]
Posted in reply to mmagnuson

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

 

View solution in original post


All Replies
Solution
‎09-13-2017 12:34 PM
Super User
Super User
Posts: 7,162

Re: Prompts erroring in query

[ Edited ]
Posted in reply to mmagnuson

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

 

Contributor
Posts: 42

Re: Prompts erroring in query

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

thank you!

 

Super User
Super User
Posts: 7,162

Re: Prompts erroring in query

Posted in reply to mmagnuson

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.

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 272 views
  • 0 likes
  • 2 in conversation