SAS Enterprise Guide

Desktop productivity for business analysts and programmers
BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
tijanagilic
Obsidian | Level 7

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:

tijanagilic_0-1704287712052.png

 

Thanks in advance!

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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

View solution in original post

14 REPLIES 14
Kurt_Bremser
Super User
%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
Obsidian | Level 7
What do I put in the filter?
%first_day_previous_month and %last_day_previous_month ?
PaigeMiller
Diamond | Level 26

@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
tijanagilic
Obsidian | Level 7
I've done it, but it's not giving me data, just an empty table.
PaigeMiller
Diamond | Level 26

@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
tijanagilic
Obsidian | Level 7
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
Super User Tom
Super User

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
Obsidian | Level 7
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
Diamond | Level 26

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
tijanagilic
Obsidian | Level 7
It worked!

Thank you all very much!
Tom
Super User Tom
Super User

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
Diamond | Level 26

@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

sas-innovate-white.png

Our biggest data and AI event of the year.

Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.

Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.

 

Register now!

Creating Custom Steps in SAS Studio

Check out this tutorial series to learn how to build your own steps in SAS Studio.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 14 replies
  • 5683 views
  • 2 likes
  • 4 in conversation