BookmarkSubscribeRSS Feed
Fol_
Calcite | Level 5

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?

12 REPLIES 12
Haikuo
Onyx | Level 15

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

sassharper
Calcite | Level 5

try to use INTCK function

art297
Opal | Level 21

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.

Ksharp
Super User

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

Fol_
Calcite | Level 5

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

art297
Opal | Level 21

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

Fol_
Calcite | Level 5

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.

art297
Opal | Level 21

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.


Fol_
Calcite | Level 5

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

art297
Opal | Level 21

Then one more suggestion.  You might want to use:

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

Fol_
Calcite | Level 5

Thanks a lot

HB
Barite | Level 11 HB
Barite | Level 11

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 1734 views
  • 6 likes
  • 6 in conversation