topic Using prompts and calculations as query limits - how? in SAS Enterprise Guide

Using prompts and calculations as query limits - how?

Proulx06 — Thu, 11 Oct 2012 13:51:49 GMT

Hi all,

Relatively new SAS EG 4.3 user, as our enterprise just switched from Hyperion. I'm LOVING the functionality EG provides.

I have a simple query with one table. I'm going to simplify the build below for purposes of explanation.

TABLE 1

Field 1

Date 1

Dollar Amount 1

Row Insert Effective Date

Row Insert Expiration Date

What I want to be able to do is build a query that brings back the value in 'Dollar Amount 1' at a certain point in time (we call this 'time traveling' in our analytics group). Our datamart allows for this by manipulating the values in the Row Insert fields.

I have successfully run this query by using this limit:

Row Insert Effective Date = Date 1 + 30 (or 60, or 90, or...)

But ideally, in order to create a template query, I want to have a prompt called "NumberOfDays", which is set to be an integer between 0 and 365. Then my limit would be:

Row Insert Effective Date = Date 1 + NumberOfDays

I get an error when I try to use this prompt within a limit calculation. The error is "ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, a missing value, BTRIM, INPUT, PUT, SUBSTRING, USER."

Am I stuck hard-coding this with the actual number of days, or is there just a syntax issue when using a prompt within a calculation in a query limit?

Thanks all!

Re: Using prompts and calculations as query limits - how?

ChrisHemedinger — Thu, 11 Oct 2012 14:02:28 GMT

Are you using the prompt for a filter? If so, then be sure to check the box "Generate filter for a prompt value" on the Filter definition window. That will generate the appropriate syntax (%eg_whereparam) that will "unwrap" the prompt value from its macro format into a value that's good for the query.

Chris

Re: Using prompts and calculations as query limits - how?

Proulx06 — Thu, 11 Oct 2012 15:31:27 GMT

I ended up finding the solution in another topic thread. What I failed to do was go into the query options and register the prompt in the Query Options. Once I did that it prompted me for the value and the plugged that value into my limit calculation.