Help using Base SAS procedures

Counting number of months

Reply
Occasional Contributor
Posts: 11

Counting number of months

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?

Respected Advisor
Posts: 3,156

Re: Counting number of months

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

New Contributor
Posts: 3

Counting number of months

try to use INTCK function

PROC Star
Posts: 7,471

Counting number of months

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.

Super User
Posts: 10,023

Counting number of months

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

Occasional Contributor
Posts: 11

Counting number of months

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

PROC Star
Posts: 7,471

Counting number of months

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

Occasional Contributor
Posts: 11

Counting number of months

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.

PROC Star
Posts: 7,471

Re: Counting number of months

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.


Occasional Contributor
Posts: 11

Counting number of months

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

PROC Star
Posts: 7,471

Counting number of months

Then one more suggestion.  You might want to use:

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

Occasional Contributor
Posts: 11

Counting number of months

Thanks a lot

Regular Contributor
Regular Contributor
Posts: 156

Counting number of months

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

Ask a Question
Discussion stats
  • 12 replies
  • 349 views
  • 6 likes
  • 6 in conversation