Help using Base SAS procedures

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

Reply
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

ID1ID2OPERdatetrnsdate
678912013Q32013Q4
678912013Q32014Q1
678912013Q32014Q2
563412013Q12013Q1
563412013Q12013Q2
563412013Q12013Q3
563412013Q12013Q4
563412013Q12014Q1
563412013Q12014Q2
222122012Q22012Q4
222122012Q22013Q1
222122012Q22013Q2
222122012Q22013Q3
637302013Q32013Q3
637302013Q32013Q4
637302013Q32014Q1
637302013Q32014Q2
89912013Q22013Q2
89912013Q22013Q3
89912013Q22013Q4
89912013Q22014Q1
89912013Q22014Q2
4637482013Q42014Q1
4637482013Q42014Q2
4637482013Q42014Q3
821302013Q32014Q2
821302013Q32014Q3
645712009Q22010Q3
645712009Q22010Q4
645712009Q22011Q1
645712009Q22011Q2
645712009Q22011Q3
902442011Q12011Q4
902442011Q12012Q1
902442011Q12012Q2
902442011Q12012Q3

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

ID1ID2OPERdatetrnsdateOPERDT_HalfYrage_YR_OPDTage_QTR_OPDTage_HalfYR_OPDT
678912013Q32013Q4AYH21266
678912013Q32014Q1AYH21599
678912013Q32014Q2AYH2181212
563412013Q12013Q1AYH1333
563412013Q12013Q2AYH1666
563412013Q12013Q3AYH1999
563412013Q12013Q4AYH1121212
563412013Q12014Q1AYH1151515
563412013Q12014Q2AYH1181818
222122012Q22012Q4AYH112129
222122012Q22013Q1AYH1151512
222122012Q22013Q2AYH1181815
222122012Q22013Q3AYH1212118
637302013Q32013Q3AYH2933
637302013Q32013Q4AYH21266
637302013Q32014Q1AYH21599
637302013Q32014Q2AYH2181212
89912013Q22013Q2AYH1636
89912013Q22013Q3AYH1969
89912013Q22013Q4AYH112912
89912013Q22014Q1AYH1151215
89912013Q22014Q2AYH1181518
4637482013Q42014Q1AYH21569
4637482013Q42014Q2AYH218912
4637482013Q42014Q3AYH2211212
821302013Q32014Q2AYH2181212
821302013Q32014Q3AYH2211512
645712009Q22010Q3AYH1211821
645712009Q22010Q4AYH1242124
645712009Q22011Q1AYH1272427
645712009Q22011Q2AYH1302730
645712009Q22011Q3AYH1333033
902442011Q12011Q4AYH1121212
902442011Q12012Q1AYH1151515
902442011Q12012Q2AYH1181818
902442011Q12012Q3AYH1212121
Super User
Super User
Posts: 7,401

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:

ID1ID2OPERdatetrnsdateOPERDT_HalfYrage_YR_OPDTage_QTR_OPDTage_HalfYR_OPDT
678912013Q32013Q4AYH21266
678912013Q32014Q1AYH21599

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

Ask a Question
Discussion stats
  • 3 replies
  • 227 views
  • 0 likes
  • 3 in conversation