Contributor
Posts: 68

# Calculate "age" of year, QTR, Half-year untl a Transaction date

Hello,

I have a dataset grouped by ID1 & ID2, an Operation_Date (OPERDATE) a TRANSDATE

DATA have

 ID1 ID2 OPERdate trnsdate 6789 1 2013Q3 2013Q4 6789 1 2013Q3 2014Q1 6789 1 2013Q3 2014Q2 5634 1 2013Q1 2013Q1 5634 1 2013Q1 2013Q2 5634 1 2013Q1 2013Q3 5634 1 2013Q1 2013Q4 5634 1 2013Q1 2014Q1 5634 1 2013Q1 2014Q2 222 12 2012Q2 2012Q4 222 12 2012Q2 2013Q1 222 12 2012Q2 2013Q2 222 12 2012Q2 2013Q3 637 30 2013Q3 2013Q3 637 30 2013Q3 2013Q4 637 30 2013Q3 2014Q1 637 30 2013Q3 2014Q2 899 1 2013Q2 2013Q2 899 1 2013Q2 2013Q3 899 1 2013Q2 2013Q4 899 1 2013Q2 2014Q1 899 1 2013Q2 2014Q2 4637 48 2013Q4 2014Q1 4637 48 2013Q4 2014Q2 4637 48 2013Q4 2014Q3 821 30 2013Q3 2014Q2 821 30 2013Q3 2014Q3 645 71 2009Q2 2010Q3 645 71 2009Q2 2010Q4 645 71 2009Q2 2011Q1 645 71 2009Q2 2011Q2 645 71 2009Q2 2011Q3 902 44 2011Q1 2011Q4 902 44 2011Q1 2012Q1 902 44 2011Q1 2012Q2 902 44 2011Q1 2012Q3

I want to add the following columns

1.- OPERDT_HalfYr  = that shows in which Half of the Year the OPERDATE takes place

2.- age_YR_OPDT   =  that shows the "age" of the year that the OPERDATE took place  until the TRANSDATE

3.-- age_QTR_OPDT = that shows the "age" of the quarter that the OPERDATE took place  until the TRANSDATE

4.-- age_HalfYR_OPDT = that shows the "age" of the half-year that the OPERDATE took place  until the TRANSDATE

as shown on the data WANT below

Thank you

Best

Nikos

Data WANT

 ID1 ID2 OPERdate trnsdate OPERDT_HalfYr age_YR_OPDT age_QTR_OPDT age_HalfYR_OPDT 6789 1 2013Q3 2013Q4 AYH2 12 6 6 6789 1 2013Q3 2014Q1 AYH2 15 9 9 6789 1 2013Q3 2014Q2 AYH2 18 12 12 5634 1 2013Q1 2013Q1 AYH1 3 3 3 5634 1 2013Q1 2013Q2 AYH1 6 6 6 5634 1 2013Q1 2013Q3 AYH1 9 9 9 5634 1 2013Q1 2013Q4 AYH1 12 12 12 5634 1 2013Q1 2014Q1 AYH1 15 15 15 5634 1 2013Q1 2014Q2 AYH1 18 18 18 222 12 2012Q2 2012Q4 AYH1 12 12 9 222 12 2012Q2 2013Q1 AYH1 15 15 12 222 12 2012Q2 2013Q2 AYH1 18 18 15 222 12 2012Q2 2013Q3 AYH1 21 21 18 637 30 2013Q3 2013Q3 AYH2 9 3 3 637 30 2013Q3 2013Q4 AYH2 12 6 6 637 30 2013Q3 2014Q1 AYH2 15 9 9 637 30 2013Q3 2014Q2 AYH2 18 12 12 899 1 2013Q2 2013Q2 AYH1 6 3 6 899 1 2013Q2 2013Q3 AYH1 9 6 9 899 1 2013Q2 2013Q4 AYH1 12 9 12 899 1 2013Q2 2014Q1 AYH1 15 12 15 899 1 2013Q2 2014Q2 AYH1 18 15 18 4637 48 2013Q4 2014Q1 AYH2 15 6 9 4637 48 2013Q4 2014Q2 AYH2 18 9 12 4637 48 2013Q4 2014Q3 AYH2 21 12 12 821 30 2013Q3 2014Q2 AYH2 18 12 12 821 30 2013Q3 2014Q3 AYH2 21 15 12 645 71 2009Q2 2010Q3 AYH1 21 18 21 645 71 2009Q2 2010Q4 AYH1 24 21 24 645 71 2009Q2 2011Q1 AYH1 27 24 27 645 71 2009Q2 2011Q2 AYH1 30 27 30 645 71 2009Q2 2011Q3 AYH1 33 30 33 902 44 2011Q1 2011Q4 AYH1 12 12 12 902 44 2011Q1 2012Q1 AYH1 15 15 15 902 44 2011Q1 2012Q2 AYH1 18 18 18 902 44 2011Q1 2012Q3 AYH1 21 21 21
Super User
Posts: 9,599

## Re: Calculate "age" of year, QTR, Half-year untl a Transaction date

Hi,

Make sure your year/quarter variables are numeric year and quarter, then use interval functions on them:

SAS(R) 9.2 Language Reference: Concepts, Second Edition

INTCX, and INTCK

Contributor
Posts: 22

## Re: Calculate "age" of year, QTR, Half-year untl a Transaction date

I'm missing something in your 'want' data:

 ID1 ID2 OPERdate trnsdate OPERDT_HalfYr age_YR_OPDT age_QTR_OPDT age_HalfYR_OPDT 6789 1 2013Q3 2013Q4 AYH2 12 6 6 6789 1 2013Q3 2014Q1 AYH2 15 9 9

ID1 = 6789 line-1 : age_YR_OPDT is 0 years between Q3 and Q4 of the same year (2013), not 12. age_QTR_OPDT is 1 and age_HalfYR_OPDT is no more than 1.

Are you looking at months?

For a date informat with OPERdate and trnsdate you should be able to use YYQ6. (example of 1991Q1 comes from Professional SAS Programming Secrets).

Then set OPERDT_HalfYR to     intck('DTYEAR',trnsdate,OPERdate);

age_YR_OPDT = intck('DTMONTH',trnsdate,OPERdate);     or 'year' if you really want years.

age_QTR_OPDT = intck('DTQTR',trnsdate,OPERdate);

age_HalfYR_OPDT = (intck('DTQTR',trnsdate,OPERdate) / 2);     there isn't a half-year function, you could try dividing the DTQTR by 2.

Contributor
Posts: 68

## Re: Calculate "age" of year, QTR, Half-year untl a Transaction date

Hi,

Yes, I am looking to get the total number of months elapsed

Thank you

Discussion stats
• 3 replies
• 261 views
• 0 likes
• 3 in conversation