topic Re: End of Month function in SAS Programming

End of Month function

thomask23 — Wed, 04 Mar 2015 18:06:35 GMT

I need to create a end of month field using a current date field. The assignment date field has mutliple dates based on the actual assignment date. For charting purposes i need to have only one date that corresponds to each month. I have tried the below, however it does not populate anything. Please help.

THE ASSIGNMENT DATA TYEP IS DATE

Data work.Data;

set work.Data ;
lastDay=intnx('month',AssignmentDte,0,'E');

RUN;

QUIT;

Re: End of Month function

billfish — Wed, 04 Mar 2015 18:28:42 GMT

Apparently your variable is a character variable. need to transform into a date variable.

Data t_a;
     lastDay1=intnx('month',today(),0,'E');
     a1 = 20000;
     lastDay2=intnx('month',a1,0,'E');
     a2 = '20140415';
     lastDay3=intnx('month',input(a2, yymmdd8.),0,'E');
     format lastday1 lastday2 lastday3 date9.;
RUN;

dataset t_a gives:
==========================================================
lastDay1 a1 lastDay2 a2 lastDay3

31MAR2015 20000 31OCT2014 20140415 30APR2014
==========================================================

Re: End of Month function

thomask23 — Wed, 04 Mar 2015 19:00:35 GMT

That syntax isnt working for me. Perhaps there is something going on with my data that i need to figure out. as a workaround, Is there a way to only return the month and the year?

Re: End of Month function

Reeza — Wed, 04 Mar 2015 19:36:34 GMT

Format the variable.

Also, if you're learning to program don't make the name in the DATA statement be the same as the name in the SET statement. This will overwrite your original data set and make it hard to find/debug code.

You will need to recreate the 'data' dataset before you can go ahead.

Data Data2;

set work.Data ;
lastDay=intnx('month',AssignmentDte,0,'E');

format lastDay yearmon7.;

RUN;

Re: End of Month function

art297 — Wed, 04 Mar 2015 19:37:08 GMT

What does your log show when you run your original code?

Re: End of Month function

thomask23 — Wed, 04 Mar 2015 20:17:41 GMT

NOTE: Invalid argument to function INTNX at line 21 column 15.

ASSIGNMENTDTE=07JAN2014:00:00:00.000000

Re: End of Month function

art297 — Wed, 04 Mar 2015 20:50:43 GMT

Data work.want;

set work.Data ;
lastDay=intnx('month',datepart(AssignmentDte),0,'E');

RUN;

Re: End of Month function

Patrick — Wed, 04 Mar 2015 21:57:46 GMT

or if you want to stay with datetime values:

Data work.want;

set work.Data ;

attrib lastDay datetime20.;
lastDay=intnx('dtmonth',AssignmentDte,0,'E');

RUN;

For reporting purposes just use a different format for lastDay with prints the internal SAS datetime value in the way you want it to.