BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Proulx06
Calcite | Level 5

I have a query that has several prompts where a user will enter in date ranges. I have registered the prompts such that the first query requires the user input, and I've checked the "use prompt values throughout project" checkbox. That part works fine.

But in my second query, I'd like to be able to use the prompt values in a calculated column. Specifically, I want to be able to say "CASE WHEN (DATEX >= PROMPTDATE1 AND DATEX<=PROMPTDATE2) THEN "BLAHBLAH"". I have done some googling, but haven't found the syntax to be able to do this. And where I'm using SAS EG and the gui interface, it doesn't allow me the option of selecting the prompts anywhere.

Can anyone help?

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

Because its a date you need to format it the way SAS needs a date. I'd look into Chris suggestion as well, he's pretty much the EG go to guy, I'm more on BASE/programming.

CASE WHEN (t1.POL_EFF_DATE>="&BEGIN_LIFE_START"d AND t1.POL_EFF_DATE<="&BEGIN_LIFE_END"d)



View solution in original post

10 REPLIES 10
ChrisHemedinger
Community Manager

Check out this post from Angela:

SAS Enterprise Guide Prompting Examples using %_eg_WhereParam

Chris

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
Reeza
Super User

Use the the name of the prompt with an & before.

Prompts create macro variables so you can read up on how to use those if you'd like.

CASE WHEN (DATEX >= &PROMPTDATE1 AND DATEX<=&PROMPTDATE2)

Proulx06
Calcite | Level 5

Reeza, your answer seemed most appropriate, but I'm getting this error in the log. You can see my computed column syntax as well. For the record, I entered values for all of these prompts in the prior query, and they appeared to have passed through correctly, as evidenced by the '12Mar2013' date, which is correct.

465            /* LIFECYCLE BUCKETS */

466              (CASE WHEN (t1.POL_EFF_DATE>=&BEGIN_LIFE_START AND t1.POL_EFF_DATE<=&BEGIN_LIFE_END) THEN "BEGIN LIFECYCLE"

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

466  12Mar2013

       -------

       22

        -------

        76

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

              GT, GTT, LE, LET, LT, LTT, NE, NET, OR, ^=, |, ||, ~=. 

ERROR 76-322: Syntax error, statement will be ignored.

467              WHEN (t1.POL_EFF_DATE>=&MID_LIFE_START AND t1.POL_EFF_DATE<=&MID_LIFE_END) THEN "MIDDLE LIFECYCLE" WHEN

468              (t1.POL_EFF_DATE>=&END_LIFE_START AND t1.POL_EFF_DATE<=&END_LIFE_END) THEN "END LIFECYCLE" ELSE "" END) AS

469              'LIFECYCLE BUCKETS'n

Reeza
Super User

Because its a date you need to format it the way SAS needs a date. I'd look into Chris suggestion as well, he's pretty much the EG go to guy, I'm more on BASE/programming.

CASE WHEN (t1.POL_EFF_DATE>="&BEGIN_LIFE_START"d AND t1.POL_EFF_DATE<="&BEGIN_LIFE_END"d)



Proulx06
Calcite | Level 5

Reeza - so am I on the right track, and just need to tweak the formatting a bit? Or do I need to scrap this whole idea of using prompts in my calcs? As you can see, I'm trying to use the date ranges the end user enters and put the line items from my results into distinct buckets based on their eff_date field.

I watched the videos Chris linked to, but unless I'm missing something, it only shows how to establish the prompts, which I've already done. It doesn't seem to address what I'm trying to do, which is to use the values the end user types into the prompts in a computed column. There is a comment on the link Chris posted alluding to variable prompts and stored processes...should I go looking in that direction?

Reeza
Super User

You just need to add the quotes and the d to tell SAS its a date, see my post above and look at how I formatted the dates.

Prompts/Macro variables are stored as characters so they need tweaking sometimes to work in other areas.

Proulx06
Calcite | Level 5

Ah, I didn't even notice the quotes and 'd' you added, my mistake. It worked. Thank you!

Shantaram
Calcite | Level 5

i am using the same thing but it display the error.

PROC SQL;

5          Create table WORK.A88 as

6          SELECT  DISTINCT

7            ( SUM(table0.CLR_BAL_AMT) where  ( table1.ACCT_OPEN_DT ) = '&Report_Date'd )  AS DIR_2 LABEL='Total Amount available on

                                       _____

                                       22

                                       76

7        !  all alive Accounts'

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,

              CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=. 

ERROR 76-322: Syntax error, statement will be ignored.

Cynthia_sas
SAS Super FREQ

Hi:

  Macro variable values do NOT resolve when placed in single quotes, as you show here:

SELECT  DISTINCT

      ( SUM(table0.CLR_BAL_AMT) where  ( table1.ACCT_OPEN_DT ) = '&Report_Date'd )

So, instead, I recommend changing your single quotes to double quotes:

SELECT  DISTINCT

      ( SUM(table0.CLR_BAL_AMT) where  ( table1.ACCT_OPEN_DT ) = "&Report_Date"d )

  

cynthia

Haikuo
Onyx | Level 15

If I understand you correctly, you are talking about "cascade prompts", which are unfortunately only supported by Stored Process.

Haikuo

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 15858 views
  • 8 likes
  • 6 in conversation