Quartz | Level 8

## How to calculate number of months between 2 dates

Hello,

New to SAS here, hoping someone can help me with this and that I am on the right track.

If I wanted to extract the date data from column 'First_date' format DDMMYY  from table 'table_X' and comparing it to a certain date to get the number of months between these 2 dates, with no decimals.

Is the logic below correct?

``````proc SQL;
create table table_X as

select
book,
First_date

From table.book.history;
quit;
Data table_X; SET table_X;
Months=YRDIF(First_date,'31Dec2017'd, ACT/ACT)*12;
put Months=;
Run;``````

Would like the end result to look like this=

 Book First_Date Months BookA 10Mar17 10 BookB 12Oct16 14 BookC 12Dec16 12

greatly appreciate any help!

1 ACCEPTED SOLUTION

Accepted Solutions
Quartz | Level 8

## Re: How to calculate number of months between 2 dates

ok, then - would this not generate the number of months taking the data from the table?

``````proc SQL;
create table table_X as
select book, First_date,
intck('month',First_date,'31Dec2017'd) as Months
from ````table.book.history``; quit;````

10 REPLIES 10
Opal | Level 21

## Re: How to calculate number of months between 2 dates

I don't understand why bookA would be 10 months. Shouldn't it be 9?

Also, how are you defining months? Number of month boundaries? i.e., would the difference between 20jan2017 and 1feb2017 be 1 or 0?

Art, CEO, AnalystFinder.com

Opal | Level 21

## Re: How to calculate number of months between 2 dates

If you are only looking for month boundaries crossed you could use:

```data history;
input Book \$ First_Date date9.;
format First_Date date9.;
cards;
BookA 10Mar17
BookB 12Oct16
BookC 12Dec16
;

proc SQL;
create table table_X as
select book, First_date,
intck('month',First_date,'31Dec2017'd) as Months
from history
;
quit;
```

Art, CEO, AnalystFinder.com

Quartz | Level 8

## Re: How to calculate number of months between 2 dates

Thank you

question, want to be able to do this by extracting dates from the table - for argument case, let's say there are an infinite amount of variables listed in the 'book' column. Would it be possible to use the logic you provided to do that?

something like?

``````data history;
input Book \$ First_Date date9.;
format First_Date date9.;
quit;

proc SQL;
create table table_X as
select book, First_date,
intck('month',First_date,'31Dec2017'd) as Months
from history
;
quit;``````

Opal | Level 21

## Re: How to calculate number of months between 2 dates

You'd have to provide an example of what you have and exactly what you want to extract.

Art, CEO, AnalystFinder.com

Quartz | Level 8

## Re: How to calculate number of months between 2 dates

lols, I thought I did. ok, so example; from the table, 'table_X'  below, want to get the number of months (as a whole number without any decimals) by using the date data column in 'First_Date' and comparing it to a certain date eg 31dec2017

Table: table_X

 Book First_Date Book1 10-Mar-17 Book2 12-Oct-16 Book3 12-Dec-16 Book4 10-Mar-17 Book5 12-Oct-16 Book6 12-Dec-16 Book7 10-Mar-17 Book8 12-Oct-16 Book9 12-Dec-16 Book10 10-Mar-17 Book11 12-Oct-16 Book12 12-Dec-16 Book13 10-Mar-17 Book14 12-Oct-16 Book15 12-Dec-16 Book16 10-Mar-17 Book17 12-Oct-16 Book18 12-Dec-16 Book19 10-Mar-17 Book20 12-Oct-16 Book21 12-Dec-16 Book22 12-Dec-16

using the example you provided - would this logic get the desired results?

``````data history;
input Book \$ First_Date date9.;
format First_Date date9.;
quit;

proc SQL;
create table table_X as
select book, First_date,
intck('month',First_date,'31Dec2017'd) as Months
from history
;
quit;``````

want to get this....  does that make sense?

 Book First_Date Months Book1 10-Mar-17 9 Book2 12-Oct-16 14 Book3 12-Dec-16 12 Book4 10-Mar-17 9 Book5 12-Oct-16 14 Book6 12-Dec-16 12 Book7 10-Mar-17 9 Book8 12-Oct-16 14 Book9 12-Dec-16 12 Book10 10-Mar-17 9 Book11 12-Oct-16 14 Book12 12-Dec-16 12 Book13 10-Mar-17 9 Book14 12-Oct-16 14 Book15 12-Dec-16 12 Book16 10-Mar-17 9 Book17 12-Oct-16 14 Book18 12-Dec-16 12 Book19 10-Mar-17 9 Book20 12-Oct-16 14 Book21 12-Dec-16 12 Book22 12-Dec-16 12

thank you again 🙂

Opal | Level 21

## Re: How to calculate number of months between 2 dates

That is what the code I suggested was intended to do. However, the first datastep in your example shows you creating the history file, but without any data.

Art, CEO, AnalystFinder.com

Quartz | Level 8

## Re: How to calculate number of months between 2 dates

would this logic not work? would I not be able to extract the date data from the column directly from the table?

``````proc SQL;
create table history as
select book, First_date``````From table.book.history;
quit;
data history;
input Book \$ First_Date date9.;
format First_Date date9.;
quit;

proc SQL;
create table table_X as
select book, First_date,
intck('month',First_date,'31Dec2017'd) as Months
from history;
quit;

``````

Opal | Level 21

## Re: How to calculate number of months between 2 dates

Your question confuses me! You really ought to start new questions with a new thread.

However,

```data history;
input Book \$ First_Date date9.;
format First_Date date9.;
quit;
```

Creates an empty dataset.

Art, CEO, AnalystFinder.com

Quartz | Level 8

## Re: How to calculate number of months between 2 dates

ok, then - would this not generate the number of months taking the data from the table?

``````proc SQL;
create table table_X as
select book, First_date,
intck('month',First_date,'31Dec2017'd) as Months
from ````table.book.history``; quit;````

Opal | Level 21

## Re: How to calculate number of months between 2 dates

what is: table.book.history?

if history is in your work library then:

from history

would work.

If table were in your book library then:

from book.history

would work

I have no idea of what table.book.history means.

Art, CEO, AnalystFinder.com

Discussion stats
• 10 replies
• 32327 views
• 4 likes
• 2 in conversation