topic Re: Using a prompt value in a computed column in SAS Enterprise Guide

Using a prompt value in a computed column

Proulx06 — Wed, 10 Apr 2013 15:09:01 GMT

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?

Re: Using a prompt value in a computed column

ChrisHemedinger — Wed, 10 Apr 2013 15:21:41 GMT

Check out this post from Angela:

SAS Enterprise Guide Prompting Examples using %_eg_WhereParam

Chris

Re: Using a prompt value in a computed column

Reeza — Wed, 10 Apr 2013 15:22:39 GMT

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)

Re: Using a prompt value in a computed column

Haikuo — Wed, 10 Apr 2013 15:23:39 GMT

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

Haikuo

Re: Using a prompt value in a computed column

Proulx06 — Wed, 10 Apr 2013 15:41:24 GMT

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

-------

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

Re: Using a prompt value in a computed column

Reeza — Wed, 10 Apr 2013 15:44:39 GMT

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)

Re: Using a prompt value in a computed column

Proulx06 — Wed, 10 Apr 2013 15:51:38 GMT

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?

Re: Using a prompt value in a computed column

Reeza — Wed, 10 Apr 2013 15:56:08 GMT

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.

Re: Using a prompt value in a computed column

Proulx06 — Wed, 10 Apr 2013 16:04:39 GMT

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

Re: Using a prompt value in a computed column

Shantaram — Fri, 22 May 2015 09:53:34 GMT

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

_____

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.

Re: Using a prompt value in a computed column

Cynthia_sas — Mon, 25 May 2015 15:42:44 GMT

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