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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.