Desktop productivity for business analysts and programmers

Using a prompt value in a computed column

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

Using a prompt value in a computed column

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?


Accepted Solutions
Solution
‎04-10-2013 11:44 AM
Grand Advisor
Posts: 17,420

Re: Using a prompt value in a computed column

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


All Replies
Community Manager
Posts: 2,697

Re: Using a prompt value in a computed column

Grand Advisor
Posts: 17,420

Re: Using a prompt value in a computed column

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)

Occasional Contributor
Posts: 12

Re: Using a prompt value in a computed column

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

Solution
‎04-10-2013 11:44 AM
Grand Advisor
Posts: 17,420

Re: Using a prompt value in a computed column

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)



Occasional Contributor
Posts: 12

Re: Using a prompt value in a computed column

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?

Grand Advisor
Posts: 17,420

Re: Using a prompt value in a computed column

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.

Occasional Contributor
Posts: 12

Re: Using a prompt value in a computed column

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

Contributor
Posts: 32

Re: Using a prompt value in a computed column

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.

SAS Super FREQ
Posts: 8,721

Re: Using a prompt value in a computed column

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

Respected Advisor
Posts: 3,124

Re: Using a prompt value in a computed column

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

Haikuo

☑ This topic is SOLVED.

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

Discussion stats
  • 10 replies
  • 6055 views
  • 7 likes
  • 6 in conversation