topic Re: First and last day of previous month in SAS Enterprise Guide

First and last day of previous month

tijanagilic — Wed, 03 Jan 2024 13:18:18 GMT

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!

Re: First and last day of previous month

Kurt_Bremser — Wed, 03 Jan 2024 13:51:39 GMT

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

Re: First and last day of previous month

tijanagilic — Wed, 03 Jan 2024 14:03:36 GMT

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

Re: First and last day of previous month

Kurt_Bremser — Wed, 03 Jan 2024 14:13:18 GMT

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

Re: First and last day of previous month

PaigeMiller — Wed, 03 Jan 2024 14:16:50 GMT

@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

Re: First and last day of previous month

tijanagilic — Wed, 03 Jan 2024 14:19:21 GMT

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

Re: First and last day of previous month

Kurt_Bremser — Wed, 03 Jan 2024 14:26:14 GMT

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

Re: First and last day of previous month

PaigeMiller — Wed, 03 Jan 2024 14:34:40 GMT

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

Re: First and last day of previous month

tijanagilic — Wed, 03 Jan 2024 14:51:26 GMT

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.

Re: First and last day of previous month

Tom — Wed, 03 Jan 2024 14:57:57 GMT

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?

Re: First and last day of previous month

PaigeMiller — Wed, 03 Jan 2024 15:16:02 GMT

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

Re: First and last day of previous month

tijanagilic — Wed, 03 Jan 2024 15:10:43 GMT

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.

Re: First and last day of previous month

tijanagilic — Wed, 03 Jan 2024 15:17:24 GMT

It worked!

Thank you all very much!

Re: First and last day of previous month

Tom — Wed, 03 Jan 2024 15:38:33 GMT

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.

Re: First and last day of previous month

PaigeMiller — Wed, 03 Jan 2024 17:35:51 GMT

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