First and last day of previous month

[tijanagilic]

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!

[Kurt_Bremser]

%let first_day_previous_month = %sysfunc(intnx(month,%sysfunc(today()),-1,b));
%let last_day_previous_month = %sysfunc(intnx(month,%sysfunc(today()),-1,e));

[tijanagilic]

What do I put in the filter?
%first_day_previous_month and %last_day_previous_month ?

[Kurt_Bremser]

Yes. The macro variables contain the raw date value (count of days from 1960-01-01) and can be used in calculations and comparisons.

[PaigeMiller]

---

@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

[tijanagilic]

I've done it, but it's not giving me data, just an empty table.

[Kurt_Bremser]

Is open_date a true SAS date value? Please run PROC CONTENTS on your dataset and show the output for the variable.

[PaigeMiller]

---

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

[tijanagilic]

I have around 8.000 rows when i do it 'manually'.

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.

[Tom]

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?

[tijanagilic]

I am using Enterprise Guide.

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.

[PaigeMiller · Accepted solution]

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));

[tijanagilic]

It worked!

Thank you all very much!

[Tom]

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.

[PaigeMiller]

---

@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.