BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
seven_snow
Calcite | Level 5

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
1 ACCEPTED SOLUTION

Accepted Solutions
TriciaA
Calcite | Level 5

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

19 REPLIES 19
Ksharp
Super User

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
Calcite | Level 5

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
Calcite | Level 5

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
Calcite | Level 5

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
Quartz | Level 8 NN
Quartz | Level 8

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

NN
Quartz | Level 8 NN
Quartz | Level 8

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
Calcite | Level 5

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
TriciaA
Calcite | Level 5

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
Calcite | Level 5

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
Calcite | Level 5

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
TriciaA
Calcite | Level 5

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
Calcite | Level 5

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
Super User

And whether you can use options symbolgen;  

to see what exactly the macro variable &date_min is .

Ksharp

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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