BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sufiya
Quartz | Level 8

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! 

 

1 ACCEPTED SOLUTION

Accepted Solutions
sufiya
Quartz | Level 8

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

10 REPLIES 10
art297
Opal | Level 21

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
Opal | Level 21

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
Quartz | Level 8

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!  

art297
Opal | Level 21

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

 

Art, CEO, AnalystFinder.com

 

sufiya
Quartz | Level 8

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 🙂

art297
Opal | Level 21

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
Quartz | Level 8

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
Opal | Level 21

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
Quartz | Level 8

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
Opal | Level 21

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

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 10 replies
  • 32327 views
  • 4 likes
  • 2 in conversation