- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hello,
I need a macro variable that I can put in the filter to put my date between the first and last day of the previous month.
For example, I want to take a column of some table and in a filter to put that column and between &first_day_previous_month and &last_day_previous_month
Example:
Thanks in advance!
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
OPEN_DATE is format 20DEC2002:00:00:00
This is the key piece of information that was missing. Your variable OPEN_DATE is not a DATE at all (I work with databases with the same problem). It is a date/time value, and so the macro variables created must contain date/time values.
%let first_day_previous_month = %sysfunc(intnx(dtmonth,%sysfunc(datetime()),-1,b));
%let last_day_previous_month = %sysfunc(intnx(dtmonth,%sysfunc(datetime()),-1,e));
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
%let first_day_previous_month = %sysfunc(intnx(month,%sysfunc(today()),-1,b));
%let last_day_previous_month = %sysfunc(intnx(month,%sysfunc(today()),-1,e));
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
%first_day_previous_month and %last_day_previous_month ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Yes. The macro variables contain the raw date value (count of days from 1960-01-01) and can be used in calculations and comparisons.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@tijanagilic wrote:
What do I put in the filter?
%first_day_previous_month and %last_day_previous_month ?
I think you have to run the code provided by @Kurt_Bremser first, before you can use these variables to filter.
Then, in the filter dialog box, you want
&first_day_previous_month
&last_day_previous_month
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Is open_date a true SAS date value? Please run PROC CONTENTS on your dataset and show the output for the variable.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@tijanagilic wrote:
I've done it, but it's not giving me data, just an empty table.
In addition to the advice from @Kurt_Bremser to show PROC CONTENTS output on this variable, I suppose it is possible that the correct answer is an empty table. Can you confirm somehow (but searching the table "manually") that there are record(s) that should be selected by this filter?
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
OPEN_DATE is format 20DEC2002:00:00:00
I am not sure what to do with:
Please run PROC CONTENTS on your dataset and show the output for the variable.
I'm new to this, so if you can be more specific, I'd appreciate it.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Are you using Enterprise Guide? If so you can create a code block and run it so that the macro variables exist.
Can you explain more completely what you are doing that is prompting you for start and stop datetimes? Can you manually enter values into the prompts and get it to work? What do the values you entered look like?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I have many tables, which contain columns of dates, the format I have specified in answer before.
When I manually want to extract data from a table, which is from a certain period, Then I make a new query, drag the columns of data that I need, and in the filter I put a column containing the date (eg open_date) between and write 12/1/23 and 12/31/23. Or another option is to write '1Dec2023:0:0:0'dt and '1Dec2023:0:0:0'dt...
As I need it to refresh monthly, I don't want to manually change those dates every time, but I want to put this between &first_day_month_previous and &last_day_month_previous, so that when I start the project, it refreshes everywhere and contains data from the previous month.
I hope it's understandable now. Sorry if it was not understandable so far.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
OPEN_DATE is format 20DEC2002:00:00:00
This is the key piece of information that was missing. Your variable OPEN_DATE is not a DATE at all (I work with databases with the same problem). It is a date/time value, and so the macro variables created must contain date/time values.
%let first_day_previous_month = %sysfunc(intnx(dtmonth,%sysfunc(datetime()),-1,b));
%let last_day_previous_month = %sysfunc(intnx(dtmonth,%sysfunc(datetime()),-1,e));
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank you all very much!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
You need to know your data.
Do any of the values in the dataset have non zero time of day parts?
Note that '31DEC2023:08:00:00'dt is greater than '31DEC2023:00:00:00'dt so filtering on values between '01DEC2023:00:00:00'dt and '31DEC2023:00:00:00t'dt will miss most of the values on the last day of the month.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@Tom wrote:
You need to know your data.
Do any of the values in the dataset have non zero time of day parts?
Note that '31DEC2023:08:00:00'dt is greater than '31DEC2023:00:00:00'dt so filtering on values between '01DEC2023:00:00:00'dt and '31DEC2023:00:00:00t'dt will miss most of the values on the last day of the month.
For the above solution, I point out that the value of &last_day_of_previous_month is 2019686399 which turns out to be 31DEC2023:23:59:59 (if run on 03JAN2024, so this handles the case you mention.
Paige Miller