How to calculate number of months between 2 dates

[sufiya]

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! 

[art297]

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

[art297]

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

[sufiya]

Thank you art297! 

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!  

[art297]

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

Art, CEO, AnalystFinder.com

[sufiya]

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 🙂

[art297]

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

[sufiya]

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;

[art297]

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

[sufiya · Accepted solution]

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;

[art297]

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