topic Calculate "age" of year, QTR, Half-year untl a Transaction date in SAS Procedures

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

Nikos — Tue, 30 Sep 2014 16:30:50 GMT

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

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

RW9 — Tue, 30 Sep 2014 16:50:33 GMT

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

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

lloydc — Wed, 01 Oct 2014 04:44:25 GMT

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.

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

Nikos — Fri, 03 Oct 2014 14:29:10 GMT

Hi,

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

Thank you