Changing Date values to a new date value

[LRogers]

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!

[novinosrin]

Methinks All you need is

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

[LRogers]

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?

[novinosrin]

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]

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!

[novinosrin · Accepted solution]

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

[LRogers]

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

I really appreciate it!

[novinosrin]

Most welcome. Have a good one! Cheers!

[Tom]

---

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

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]

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