Desktop productivity for business analysts and programmers

Date Parameters within 4.2

Reply
New Contributor
Posts: 4

Date Parameters within 4.2

Hi,
a little help please.

I have set up a date parameter where the user selects a month (typically 3 months ago using the default value of N months ago). The problem I have is that when I query this field to give me all the records within the month, it only returns records for the first day of the month.

I appreciate that dates are numbers but am unsure whether I am doing something stupid or whether EG is not set up to understand that a month has a series of numbers.

All advice & answers appreciated.

cheers
Contributor
Posts: 71

Re: Date Parameters within 4.2

Hi John,

I think that you are going to have to give us a bit more detail on this one.

My guess is that the value your user is picking off the list is a single SAS date value, such as 01Jul2009 and then that single date is being passed to a Query/Filter as a paramater (SAS macro variable), perhaps called REPORT_DATE, and so the actual SAS code making up the WHERE clause of the filter is currently something like:

WHERE ORDER_DATE=&REPORT_DATE

which in our example would resolve to:

WHERE ORDER_DATE=18079

because SAS represents the date 01Jul2009 as the integer 18079.

If you want to get the records from your source dataset that cover the whole of the month of Jul2009 (ie everything where ORDER_DATE is between and including 01Jul2009 - 31Jul2009), the Filter needs to be written as something like:

Order_Date Between &ReportDate And IntNx("Month", &ReportDate, 1)-1

When the user chooses 01Jul2009 from the pick list at run-time, the filter will resolve to something like:

Order_Date Between 18079 And 18109

Where
18079 is the SAS representation of the date 01Jul2009
and
18109 is the SAS representation of the date 31Jul2009

The user only needs to choose the 01mmmyyyy value from your pick list and then the IntNX function moves to the start of the next month, then back one day resulting in the date 31Jul2009 being passed as the second part of the Between clause.

So, in short, your parameter is probably set up OK and you need to focus on the filter that makes the query pick the records.

I hope that this is clear and actually does what you need.

Cheers

Down Under Dave.
Ask a Question
Discussion stats
  • 1 reply
  • 85 views
  • 0 likes
  • 2 in conversation