DATA Step, Macro, Functions and more

How to calculate number of months between 2 dates

Accepted Solution Solved
Reply
Contributor
Posts: 34
Accepted Solution

How to calculate number of months between 2 dates

[ Edited ]

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=

BookFirst_DateMonths
BookA10Mar1710
BookB12Oct1614
BookC12Dec1612

 

greatly appreciate any help! 

 


Accepted Solutions
Solution
‎02-03-2018 09:32 PM
Contributor
Posts: 34

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;

 

View solution in original post


All Replies
PROC Star
Posts: 8,114

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

 

 

 

PROC Star
Posts: 8,114

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

 

Contributor
Posts: 34

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;

greatly appreciate your help!  

PROC Star
Posts: 8,114

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

 

Contributor
Posts: 34

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

BookFirst_Date
Book110-Mar-17
Book212-Oct-16
Book312-Dec-16
Book410-Mar-17
Book512-Oct-16
Book612-Dec-16
Book710-Mar-17
Book812-Oct-16
Book912-Dec-16
Book1010-Mar-17
Book1112-Oct-16
Book1212-Dec-16
Book1310-Mar-17
Book1412-Oct-16
Book1512-Dec-16
Book1610-Mar-17
Book1712-Oct-16
Book1812-Dec-16
Book1910-Mar-17
Book2012-Oct-16
Book2112-Dec-16
Book2212-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?

BookFirst_DateMonths
Book110-Mar-179
Book212-Oct-1614
Book312-Dec-1612
Book410-Mar-179
Book512-Oct-1614
Book612-Dec-1612
Book710-Mar-179
Book812-Oct-1614
Book912-Dec-1612
Book1010-Mar-179
Book1112-Oct-1614
Book1212-Dec-1612
Book1310-Mar-179
Book1412-Oct-1614
Book1512-Dec-1612
Book1610-Mar-179
Book1712-Oct-1614
Book1812-Dec-1612
Book1910-Mar-179
Book2012-Oct-1614
Book2112-Dec-1612
Book2212-Dec-1612

 

thank you again Smiley Happy

PROC Star
Posts: 8,114

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

 

Contributor
Posts: 34

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_dateFrom 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;

 

PROC Star
Posts: 8,114

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

 

Solution
‎02-03-2018 09:32 PM
Contributor
Posts: 34

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;

 

PROC Star
Posts: 8,114

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

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 451 views
  • 0 likes
  • 2 in conversation