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

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

seven_snow — Fri, 02 Dec 2011 04:40:06 GMT

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)

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

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

Ksharp — Fri, 02 Dec 2011 05:27:17 GMT

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.

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

TriciaA — Fri, 02 Dec 2011 13:47:45 GMT

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

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

seven_snow — Fri, 02 Dec 2011 18:15:51 GMT

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?

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

seven_snow — Fri, 02 Dec 2011 18:24:12 GMT

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".

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

NN — Fri, 02 Dec 2011 20:05:22 GMT

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

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

TriciaA — Fri, 02 Dec 2011 20:16:42 GMT

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

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

seven_snow — Fri, 02 Dec 2011 20:19:54 GMT

yes.

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

NN — Fri, 02 Dec 2011 20:55:36 GMT

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;

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

seven_snow — Fri, 02 Dec 2011 21:57:48 GMT

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!

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

seven_snow — Fri, 02 Dec 2011 22:25:11 GMT

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

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

TriciaA — Fri, 02 Dec 2011 22:26:56 GMT

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.

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

TriciaA — Fri, 02 Dec 2011 22:31:32 GMT

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?

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

TriciaA — Fri, 02 Dec 2011 22:39:02 GMT

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

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

NN — Sat, 03 Dec 2011 11:15:05 GMT

Hi,

If your objective is to provide a date & time range or value

Then as a sample you can try using the below code in your code section

%macro report();

proc sql;

select count(*) from

sashelp.timedata where datetime

/*if using mulitple values then uncomment the below code*/

/* in (*/

/*%if &date_count = 1 %then %do;*/

/*"&date"dt*/

/*%end;*/

/*%else %do i=1 %to &date_count;*/

/*"&&date&i"dt*/
/*%end;*/

/*);*/

/*if using using a range then uncomment the below code*/

/*between "&date_min"dt and "&date_max"dt ;*/

run;

%mend;

%report;

Donot forget to use timestamp or Timestamp range as your prompt type... Same should then work..

Your Prompt Values would look something like January 12, 1998 06:00:00 AM

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

Ksharp — Mon, 05 Dec 2011 03:45:26 GMT

And whether you can use options symbolgen;

to see what exactly the macro variable &date_min is .

Ksharp

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

seven_snow — Tue, 06 Dec 2011 16:31:05 GMT

Thanks all - for your valuable inputs! My issue is resolved now.

Whether few things worked or not, I have still learnt from your comments.

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

Peter_C — Tue, 06 Dec 2011 22:15:48 GMT

a suggestion, I hope you'll find useful

When filtering a datetime value, don;t convert it to compare against date constants!

Just convert the constants, like:

where datetime_value between "&date1:0:0"dt and "&date2:23:59:59.999"dt

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

seven_snow — Wed, 07 Dec 2011 19:54:28 GMT

Peter, thanks for your suggestion, but I am afraid to confess that I did not understand your point. Please elaborate a little.

My Understanding:

"datetime_value" is a value selected from the calender that the prompts give in.

"&date1:0:0"dt" is the actual value from the table.

For example,

After executing the STP, when I select the value from the prompts' calender option, it displays: "October 01, 2010 11:09:19 AM"--(1)

Value from actual table is displayed as: "01OCT2010:00:00:00"--(2)

And you want to convey that we should not filter like: datepart(2) = datepart(1) ?

Also, I did not understand how to convert the constants? -> "&date2:23:59:59.999"dt ??

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

Peter_C — Thu, 08 Dec 2011 09:59:44 GMT

pardon me if I'm only adding confusion.

I imagined you were comparing data values stored as standard SAS datetime values. There is a datetime constant syntax. Some posters suggest extracting the datepart before comparing a datetime value with a constant date.

From your reply I presume your objective is to compare values as text, so my suggestion is inappropriate.