topic Counting number of months in SAS Procedures

Counting number of months

Fol_ — Mon, 20 Feb 2012 15:24:14 GMT

Hi,

i have a large data set that has details of when a client first made a deposit and the last date of deposit. For instance Clent A has first_date_deposit as 15/07/2003 and last_date_deposit as 24/02/2010.(this is the date format in the dataset) I want to create a new variable that will display the total number of months that has elapsed between the first date of deposit and last date of deposit.

Any suggestions?

Re: Counting number of months

Haikuo — Mon, 20 Feb 2012 15:32:40 GMT

Question: When you say total number of months, do you mean the actual calender months in between or only the months that have deposits?

For the former, all you need to apply intck() function on two dates of yours, marking 'month' as the unit of intervals; for the later, you need to count the events happening within the same customer ID, which can be done using numerous ways, such as data step, proc SQL, freq, mean etc.

Regards,

Haikuo

Counting number of months

sassharper — Mon, 20 Feb 2012 15:35:39 GMT

try to use INTCK function

Counting number of months

art297 — Mon, 20 Feb 2012 15:37:14 GMT

You can use the interval functions to determine the number of month boundaries that have to be crossed to get from one date to another, but wouldn't it be more accurate to just use the number of days?

That can be computed by just subtracting the two dates.

Counting number of months

Ksharp — Tue, 21 Feb 2012 09:42:08 GMT

If you want start date and end date has the same day when passing a month. not like intnx() only concentrate on first day every month.

data _null_;
infile datalines dlm=' ';
input start : ddmmyy12. end : ddmmyy12.;
number=intck('month',start-day(start)+1,end-day(start)+1);
put number= ;
datalines;
15/07/2003 14/08/2010
;
run;

Ksharp

Counting number of months

Fol_ — Tue, 21 Feb 2012 14:37:48 GMT

Thanks for all the suggestions. I am not familiar with proc codes.

But can i use the column label with the INTCK statement instead of date such as

no_of_month=INTCK('month', first_date_dep, last_date_dep); so counting the number of months between the first date of deposit and last date of deposit

Thanks

Counting number of months

art297 — Tue, 21 Feb 2012 14:40:27 GMT

Just as long as you are aware that Jan31st to Feb 1st will result in a greater value than Jan1st to Jan 31st.

Counting number of months

Fol_ — Tue, 21 Feb 2012 14:53:28 GMT

Thank you Art. But please can you elaborate? I'm not sure i understand or can you suggest a query for determining the number of months elapsed.

Re: Counting number of months

art297 — Tue, 21 Feb 2012 15:31:10 GMT

It is why I was suggesting using days elapsed rather than months. If you have to use months, then I would suggest ensuring that you use the 'c' alignment argument. It is easiest to show with an example:

data have;

informat date1 date2 date9.;

format date1 date2 date9.;

input date1 date2;

months1=intck('month',date1,date2);

months2=intck('month',date1,date2,'c');

days=date2-date1;

cards;

01feb2010 28feb2010

03feb2010 02mar2010

03feb2010 03mar2010

;

Running the above code will produce a file containing:

Obs date1 date2 months1 months2 days

1 01FEB2010 28FEB2010 0 0 27

2 03FEB2010 02MAR2010 1 0 27

3 03FEB2010 03MAR2010 1 1 28

As you can see, without the 'c' argument, the intck function only counts the number of month boundaries that have to be crossed to get to one date from another date.

With it, it still only counts the number of boundaries that have to be crossed, but at least using the day of the month as the boundary.

Days, on the other hand, I think gives a more accurate picture.

Counting number of months

Fol_ — Tue, 21 Feb 2012 15:39:09 GMT

Thank you Art, i agree with the argument. I will try to compute the days elapsed instead and divide that by 12 to get number of months.

Thanks

Counting number of months

art297 — Tue, 21 Feb 2012 15:51:55 GMT

Then one more suggestion. You might want to use:

months=yrdif(date1, date2,'age')*12;

Counting number of months

Fol_ — Tue, 21 Feb 2012 17:24:58 GMT

Thanks a lot

Counting number of months

HB — Tue, 21 Feb 2012 17:41:58 GMT

Wouldn't you want to take the number of days divided by 30, not 12, to get an approximation of the number of months?

270 days / 12 = 22.5

270 days / 30 = 9 months