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

[NN]

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

[seven_snow]

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

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

[Peter_C]

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

[seven_snow]

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

[Peter_C]

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.