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!
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.
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.