BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LRogers
Obsidian | Level 7

Hello,

I'm working with two different tables - one table has the dates of each month listed as the LAST day of the month (example: 31JAN2019), however my other table has the dates as the FIRST day of the month (example: 01JAN2019).

What I would like to do is change the dates that are the first day of the month to match the last day of the month.  

I included a screen shot, I'm new to SAS so obviously this is wrong - it's not working.  another thing is I would like my MONTH_LAST_DAY to be a date and right now it's coming out as #.

Or perhaps they's a last day of month format I'm not aware of that would work better??

Thank you for your help!

SAS example.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20
month_last_day=intnx('dtmon',month_first_day,0,'e')-86399;
format month_last_day datetime20.;

View solution in original post

10 REPLIES 10
novinosrin
Tourmaline | Level 20

Methinks All you need is

 

month_last_day=intnx('dtmon',month_first_day,0,'e');
format month_last_day datetime20.;

 

LRogers
Obsidian | Level 7

Thank you!!!

When i ran that code I get numbers as my date....I'm assuming I would need to add a date format to the code?SAS2.jpg

novinosrin
Tourmaline | Level 20

Hi @LRogers   yes, sorry I edited the previous adding a a datetime format. Here again

 

month_last_day=intnx('dtmon',month_first_day,0,'e');
format month_last_day datetime20.;

 

 

LRogers
Obsidian | Level 7

That worked great!  Thanks again!

One last question...Is there a way to make the time be zeros (00:00:00)?  That is how the date/time is like in the other table as well....just trying to get them to match.  Thank you!

SAS3.jpg

novinosrin
Tourmaline | Level 20
month_last_day=intnx('dtmon',month_first_day,0,'e')-86399;
format month_last_day datetime20.;
LRogers
Obsidian | Level 7

Wow!!!  thank you!  You were so helpful!  

I really appreciate it!

novinosrin
Tourmaline | Level 20

Most welcome. Have a good one! Cheers!

Tom
Super User Tom
Super User

@novinosrin wrote:
month_last_day=intnx('dtmon',month_first_day,0,'e')-86399;
format month_last_day datetime20.;

Why use the "magic" number? What the heck is 86399?

Did you mean '23:59:59't ?

novinosrin
Tourmaline | Level 20

Lol Sir @Tom  I never ever imagined you would open your sense of humor. I just fancied a direct subtraction from the end seconds value unlike the nested function. However, I was anticipating you would chime in with more eloquent and  detailed explanation and that you did. I have been taking notes of all your help that has benefited me and others across the sas world 🙂

 

Thank you as always for your generosity!

Tom
Super User Tom
Super User

So you appear from the photographs of your data (pictures of data is better than no data, but text is much much better) your variable is not a DATE.  It is a DATETIME.   And you have not set the end of the month to the actual end of the month, but to the start of the last day of the month.

 

It will probably be a lot easier to force the END to the START instead of the other way.

start_dt = intnx('dtmonth',end_dt,0,'b');

To replicate that start of the last day of the month format you would need a little more logic. A couple of examples:

end_dt = intnx('dtday',intnx('dtmonth',start_dt,0,'e'),0,'b');
end_dt = dhms(intnx('month',datepart(start_dt),0,'b'),0,0,0);

It might help to first convert your variables so that they contain actual DATE values instead of DATETIME values.

start_date = datepart(start_dt);
end_date=datepart(end_dt);
format start_date end_date date9.;

Then either conversion is just as easy.

start_date = intnx('month',end_date,0,'b');
end_date   = intnx('month',start_date,0,'e');

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 10 replies
  • 2040 views
  • 0 likes
  • 3 in conversation