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 |
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
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.
Hi,
Yes, I am looking to get the total number of months elapsed
Thank you
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.