topic Re: Intnx - enhance alignment options with '15' in SAS Procedures

Intnx - enhance alignment options with '15'

KasDunphy — Mon, 16 Jun 2014 19:01:36 GMT

Our health care company counts membership based on the 15th of the month. Currently, INTNX will return dates as of the middle of the month using the 'm' alignment argument. But this returns 15 or 16 depending on the month. I'd like to be able to specify the 15th (or even any day of the month) in the alignment argument.

Currently, we have to jump through a few hoops to get the job done.

%let ds_cur_d = today();

%let _15TH_DT = %sysfunc(mdy(%sysfunc(month(&ds_cur_d)), 15, %sysfunc(year(&ds_cur_d))), mmddyyd10.);

Is there an easier way to do this?

Re: Intnx - enhance alignment options with '15'

ballardw — Mon, 16 Jun 2014 20:01:03 GMT

How are you using the _15th_dt variable?

From the online help:

date4=intnx('month','01jan95'd,1,'sameday');

put date4 / date4 date7.;

So if you start with a 15th of the month, sameday goes to the 15th.

Re: Intnx - enhance alignment options with '15'

Reeza — Mon, 16 Jun 2014 20:07:05 GMT

You could create your own function using proc fcmp.

proc fcmp outlib=sasuser.functions.sample;

function month15(start_date);

n=intnx('month', start_date, 0, 'b')+14;

return (n);

endsub;

run;quit;

options cmplib=(sasuser.functions);

%let ds_cur_d = %sysfunc(today());

%put &ds_cur_d;

%let _15TH_DT = %sysfunc(month15(&ds_cur_d), mmddyyd10.);

%put &_15TH_DT;

Re: Intnx - enhance alignment options with '15'

Astounding — Mon, 16 Jun 2014 20:43:40 GMT

Good approach, and it can be done, but it's a little more complex than that. If the start_date is the 1st day of the month, for example, the function has to return the 15th day of the prior month (not the 15th day of the current month). A tweak to the third parameter of INTNX might do the trick. Instead of 0:

0 - (day(start_date) < 15)

Re: Intnx - enhance alignment options with '15'

Haikuo — Mon, 16 Jun 2014 20:47:51 GMT

Wondering why 'month.15' not working while the shift index seems to work on 'year' or 'week'.

Re: Intnx - enhance alignment options with '15'

Reeza — Mon, 16 Jun 2014 20:54:58 GMT

I think it would be day15 wouldn't it?

How do you know that the first resolves to the 15 of the previous month? You can probably extend the function above to take the exact same parameters as intnx but a custom one as well that would do 15, based on the question, didn't see the need to do that.

Re: Intnx - enhance alignment options with '15'

Haikuo — Mon, 16 Jun 2014 20:59:45 GMT

, day15 would be 15-day intervals starting on Sunday? While I meant (also OP?) is the 15th day of every month?

Re: Intnx - enhance alignment options with '15'

Reeza — Mon, 16 Jun 2014 21:02:59 GMT

True...I'm not too familiar with the Interval custom options, personally I would just set it to beginning and add 15

Re: Intnx - enhance alignment options with '15'

Astounding — Mon, 16 Jun 2014 21:05:18 GMT

Reeza,

That's my interpretation of the problem ... if you count as of the 15th of the month to me that means that your time periods run from the 15th of the previous month to the the 15th of the current month (one of them gets included, and one of them gets included).

Your solution matches the original posted logic. It's just that to me, if you are using the 25th day of one month or the 5th day of the next month, you should be getting the same starting date. Perhaps that's not what the poster intended however ...

Re: Intnx - enhance alignment options with '15'

KasDunphy — Thu, 19 Jun 2014 21:40:44 GMT

Thanks everyone for your feedback. Because our team uses _15TH_DT a lot , it makes sense to create a macro and share it. Thanks Reeza.

Re: Intnx - enhance alignment options with '15'

Tom — Thu, 19 Jun 2014 23:29:46 GMT

I am not sure what your exact requirements are but it should work if you subtract 15 and then use INTNX to find the beginning of the right month and then add back 14 days to get to the 15th.

data xx ;

do day = '01JAN2014'd to '16FEB2014'd ;

start = intnx('month',day-15,0)+14;

end = intnx('month',day-15,1)+14;

inside = (start < day <= end);

output;

end;

format day start end yymmdd10.;

run;

You could make the value 15 into a macro variable or the parameter to a macro.

%macro intnx_day_of_month(date,day,month);

intnx('month',&date - &day,&month)+&day-1

%mend;

data yy ;

do day = '01JAN2014'd to '16FEB2014'd ;

start = %intnx_day_of_month(day,15,0);

end = %intnx_day_of_month(day,15,1);

inside = (start < day <= end);

output;

end;

format day start end yymmdd10.;

run;

proc compare data=xx compare=yy;

run;

data