topic Re: Changing Date values to a new date value in New SAS User

Changing Date values to a new date value

LRogers — Fri, 15 Nov 2019 17:59:41 GMT

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!

Re: Changing Date values to a new date value

novinosrin — Fri, 15 Nov 2019 18:11:38 GMT

Methinks All you need is

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

Re: Changing Date values to a new date value

LRogers — Fri, 15 Nov 2019 18:16:24 GMT

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?

Re: Changing Date values to a new date value

novinosrin — Fri, 15 Nov 2019 18:19:12 GMT

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

Re: Changing Date values to a new date value

LRogers — Fri, 15 Nov 2019 18:27:26 GMT

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!

Re: Changing Date values to a new date value

novinosrin — Fri, 15 Nov 2019 18:31:08 GMT

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

Re: Changing Date values to a new date value

LRogers — Fri, 15 Nov 2019 18:33:08 GMT

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

I really appreciate it!

Re: Changing Date values to a new date value

novinosrin — Fri, 15 Nov 2019 18:34:24 GMT

Most welcome. Have a good one! Cheers!

Re: Changing Date values to a new date value

Tom — Fri, 15 Nov 2019 18:36:14 GMT

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

Re: Changing Date values to a new date value

Tom — Fri, 15 Nov 2019 18:43:54 GMT

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

Re: Changing Date values to a new date value

novinosrin — Fri, 15 Nov 2019 18:51:22 GMT

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!