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

[seven_snow]

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!!

[Ksharp]

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.

[seven_snow]

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?

[TriciaA]

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

[seven_snow]

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

Format:DATETIME20.

Informat:DATETIME20.

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

[NN]

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

[seven_snow]

yes.

[NN]

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;

[seven_snow]

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!

[TriciaA]

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.

[TriciaA]

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

[seven_snow]

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:

Variable	Type	Len	Format	Informat	Label
DATE	Num	8	DATETIME20.	DATETIME20.	DATE
Col A	Char	50	$50.00	$50.00	Col A
Col B	Char	50	$50.00	$50.00	Col B
Col D	Num	8			Col D
Col E	Num	8	27.1	27.1	Col E

[TriciaA]

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?

[TriciaA · Accepted solution]

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 

[Ksharp]

And whether you can use options symbolgen;  

to see what exactly the macro variable &date_min is .

Ksharp