DATA Step, Macro, Functions and more

Error with "WHERE %_eg_WhereParam" in STP multiple value prompt with dynamic selection

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Error with "WHERE %_eg_WhereParam" in STP multiple value prompt with dynamic selection

Hi,

I am trying to build a stored process with a "Multiple value" dynamic selection for the Date field(which was initially in Datatime20. format). Prompt is picking this date column as "Date" in prompt manager and as type "Timestamp" in Info Map Studio.

I am recieving the following error:

proc sql;

create table temp as select distinct colA, colB, date, sum(colD), sum(colE) from

libref.dataset WHERE %_eg_WhereParam( DATE, Date, IN, TYPE=D)

_

22

200

WARNING: Apparent invocation of macro _EG_WHEREPARAM not resolved.

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, CALCULATED, CASE, EXISTS, INPUT,

The SAS System

NOT, PUT, SUBSTRING, TRANSLATE, USER, ^, ~.

Later I have to group it by colA and colB as well. But I am stuck with this error. I have tried to google it out, but all in vain. Please advise.

NOTE: I am working on SAS EG 4.2. and later I want to use this stored process in BI Dashboard 4.3.

Thanks!!


SAS STP error.png

Accepted Solutions
Solution
‎12-02-2011 05:39 PM
Contributor
Posts: 40

Re: Error with "WHERE %_eg_WhereParam" in STP multiple value prompt with dynamic selection

Duh ... I just thought of it ... it's looking for Date and not datetime I bet.

Try extracting the date using datepart().

Some thing like this ... you can fill in the correct syntax:

where datepart(date) between &date_min. and &date_max 

View solution in original post


All Replies
Super User
Posts: 9,687

Error with "WHERE %_eg_WhereParam" in STP multiple value prompt with dynamic selection

Form your log, It looks like SAS can not find macro _EG_WHEREPARAM.

So make sure you have this macro and right name of macro.

Occasional Contributor
Posts: 10

Error with "WHERE %_eg_WhereParam" in STP multiple value prompt with dynamic selection

Thanks Ksharp!

I am sorry, I am pretty new to macros. Could you please tell on how to check if my SAS has this macro?

Contributor
Posts: 40

Error with "WHERE %_eg_WhereParam" in STP multiple value prompt with dynamic selection

I have better luck the %eg_whereparam when I let SAS EG build the link - using the Query Builder.  Then I just copy the code from the Query Builder.

This SAS GloFo paper talks about using the Query Builder and the %eg_whereparam.  You may have already seen it.

Interactive and Efficient Macro Programming with Prompts in SAS® Enterprise Guide® 4.2

Hope this helps:

Tricia A

SAS BI Blog

Occasional Contributor
Posts: 10

Error with "WHERE %_eg_WhereParam" in STP multiple value prompt with dynamic selection

Thanks Tricia!

I tried with Query builder as well and gives me the following error:

ERROR: Invalid date/time/datetime constant ""d.

I guess its not able to accept the datatype of the "Date" column. I tried with "D" and "DT" , but none of them seems to work. Any suggestions?

FYR:

(1) The input data has following characteristics of "Date" column:

Type:Numeric, Length(in bytes):8

Group: Date

FormatSmiley Very HappyATETIME20.

InformatSmiley Very HappyATETIME20.

(2) In Prompt Manager, Date column comes up when I select the prompt type "Date".

Regular Contributor
Regular Contributor
Posts: 166

Error with "WHERE %_eg_WhereParam" in STP multiple value prompt with dynamic selection

just a small question .. are you running your stored process in EG itself...

Occasional Contributor
Posts: 10

Error with "WHERE %_eg_WhereParam" in STP multiple value prompt with dynamic selection

yes.

Regular Contributor
Regular Contributor
Posts: 166

Error with "WHERE %_eg_WhereParam" in STP multiple value prompt with dynamic selection

Hi,

Your note

NOTE: I am working on SAS EG 4.2. and later I want to use this stored process in BI Dashboard 4.3.

got me wondering if you were executing your STP on EG or via Information Delivery Portal.

Just had a feeling that maybe %_eg_whereparam works only on EG and maynot work when running via portal.. However cannot confirm this as not in front of my application right now..

You should look at the notes provided by Tricia.. Which gives you the option for a date range.

. However in your code it looks like you are selecting a list of dates hence you can also look at something in the lines of this

proc sql;

create table temp as select distinct colA, colB, date, sum(colD), sum(colE) from

libref.dataset WHERE  DATE in (

%if &date_count  = 1 %then %do;

"&date"d

%end;

%else %do i=1 %to &date_count

"&&date&i"d

%end;

);

quit;

Occasional Contributor
Posts: 10

Re: Error with "WHERE %_eg_WhereParam" in STP multiple value prompt with dynamic selection

Thanks for your inputs!!

I tried your suggested method and the code runs with no errors. But I am not able to see any table created, nor I am able to get it in csv.

I tried to execute the same in SAS STP as well, but it throws a weird error. I have enclosed the error in the attachment of my first post and I guess now its allowing me t attach it to this post as well.strange!


SAS STP error.png
Contributor
Posts: 40

Re: Error with "WHERE %_eg_WhereParam" in STP multiple value prompt with dynamic selection

Looks like you date is formatted incorrectly ... I would expect it to look like this: 

03NOV10 13:00

Maybe some thing is actually wrong with your date variable and that's what is the root of the problem.

Contributor
Posts: 40

Error with "WHERE %_eg_WhereParam" in STP multiple value prompt with dynamic selection

Can you do a proc contents so I can see the dataset?  Tell me which values you are trying to use.

In this blog post - I show how to setup a date range prompt for a stored process - it might shed some light.  I didn't use the %eg_whereparam - I just coded it manually.  Since I've been coding so long - it's easier for me.

http://www.bi-notes.com/2011/10/prompts-how-do-i-use-a-date-range-prompt/

Tricia

Occasional Contributor
Posts: 10

Re: Error with "WHERE %_eg_WhereParam" in STP multiple value prompt with dynamic selection

Thanks again Tricia. I really appreciate your help.

(1) As mentioned in your blog-post, Its working fine with the date range in proc tabulate. I am not not sure how it would come up, when I try to execute the same in SAS BI dashboard 4.3.

But, to my surprise, when I use proc sql, it throws the following error again:

WARNING: Apparent symbolic reference DATE_MIN not resolved.

ERROR: Invalid date/time/datetime constant "&date_min."dt.

WARNING: Apparent symbolic reference DATE_MAX not resolved.

ERROR: Invalid date/time/datetime constant "&date_max."dt.

I am not sure how is the Date variable working, because again its not accepting the type"d" and "dt" in the simple where clause as well.

(2) Do you think, is there a way, where I can specify random date values instead of a continuous date range?

NOTE: To your request, PFB the contents of the mentioned dataset:

VariableTypeLenFormatInformatLabel
DATENum8DATETIME20.DATETIME20.DATE
Col AChar50$50.00 $50.00 Col A
Col BChar50$50.00 $50.00 Col B
Col DNum8 Col D
Col ENum827.127.1Col E
Contributor
Posts: 40

Re: Error with "WHERE %_eg_WhereParam" in STP multiple value prompt with dynamic selection

As I replied earlier ... sorry I just saw this post:

Based on the error - I think something is wrong with your date variable.  Can you do a proc print and show the first 5 observations?

Solution
‎12-02-2011 05:39 PM
Contributor
Posts: 40

Re: Error with "WHERE %_eg_WhereParam" in STP multiple value prompt with dynamic selection

Duh ... I just thought of it ... it's looking for Date and not datetime I bet.

Try extracting the date using datepart().

Some thing like this ... you can fill in the correct syntax:

where datepart(date) between &date_min. and &date_max 

Super User
Posts: 9,687

Re: Error with "WHERE %_eg_WhereParam" in STP multiple value prompt with dynamic selection

And whether you can use options symbolgen;  

to see what exactly the macro variable &date_min is .

Ksharp

☑ This topic is SOLVED.

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

Discussion stats
  • 19 replies
  • 2029 views
  • 8 likes
  • 5 in conversation