BookmarkSubscribeRSS Feed
JohnLonsdale
Calcite | Level 5
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
1 REPLY 1
DaveShea
Lapis Lazuli | Level 10
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.

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 1 reply
  • 613 views
  • 0 likes
  • 2 in conversation