Hello SAS Support Communities,
I think that the following does what you want. It only uses the periods1a dataset:
data periods1a; infile cards dlm=',' dsd; input CIK fyr rpb : mmddyy10. rpe : mmddyy10.; format rpb rpe mmddyy10.; cards; 1234367,12,1/1/2007,12/31/2009 1576888,7,7/1/2013,7/31/2014 2008765,5,3/1/2012,6/15/2013 6875330,12,1/1/2013,1/31/2014 9877364,9,10/1/2011,9/30/2012 ; data want; set periods1a; Periods=(intck(catt('qtr.',mod(fyr,3)+1),rpb,rpe)+1)/4; run;
Art, CEO, AnalystFinder.com
Try
quarters=intck('qtr',rpb,rpe);
if these 2 variables are sas dates.
Thank you Chris. I received output, which is progress. However, the fyr issue was not taken into consideration and, therefore, some of the results are wrong.
Please post a few lines of:
- existing data
- wanted data
I am not downloading zip files.
The lines don't have to be complete data, just a sample to illustrate the calculations you need.
Here you go Chris - thanks a lot!
Jadallah
I download Microsoft files even less.
Please post data as data steps (and check that they run fine).
Periods1a Dataset:
CIK | fyr | rpb | rpe |
1234367 | 12 | 1/1/2007 | 12/31/2009 |
1576888 | 7 | 6/30/2013 | 7/31/2014 |
2008765 | 5 | 3/1/2012 | 6/15/2013 |
6875330 | 12 | 1/1/2013 | 1/31/2014 |
9877364 | 9 | 10/1/2011 | 9/30/2012 |
Fyr is the end of a fiscal year.
Periods1b Dataset:
CIK | fyr | rpb | rpe | fyr1 | fyr1a |
1234367 | 12 | 1/1/2007 | 12/31/2009 | 13 | 1 |
1576888 | 7 | 6/30/2013 | 7/31/2014 | 8 | 8 |
2008765 | 5 | 3/1/2012 | 6/15/2013 | 6 | 6 |
6875330 | 12 | 1/1/2013 | 1/31/2014 | 13 | 1 |
9877364 | 9 | 10/1/2011 | 9/30/2012 | 10 | 10 |
Fyr1a is the beginning of a fiscal year.
Periods2 Dataset (what I want):
CIK | fyr | rpb | rpe | Periods |
1234367 | 12 | 1/1/2007 | 12/31/2009 | 3 |
1576888 | 7 | 7/1/2013 | 7/31/2014 | 1.25 |
2008765 | 5 | 3/1/2012 | 6/15/2013 | 1.5 |
6875330 | 12 | 1/1/2013 | 1/31/2014 | 1.25 |
9877364 | 9 | 10/1/2011 | 9/30/2012 | 1 |
With observation 1, there are 3 years that are covered.
With observation 2, 7/1/2013 is in the quarter that ends 7/31/2013. That is 0.25 and 1 year (8/1/2013 through 7/31/2014) to get 1.25.
God bless, best regards, and thanks again Chris!
Jadallah
I think that the following does what you want. It only uses the periods1a dataset:
data periods1a; infile cards dlm=',' dsd; input CIK fyr rpb : mmddyy10. rpe : mmddyy10.; format rpb rpe mmddyy10.; cards; 1234367,12,1/1/2007,12/31/2009 1576888,7,7/1/2013,7/31/2014 2008765,5,3/1/2012,6/15/2013 6875330,12,1/1/2013,1/31/2014 9877364,9,10/1/2011,9/30/2012 ; data want; set periods1a; Periods=(intck(catt('qtr.',mod(fyr,3)+1),rpb,rpe)+1)/4; run;
Art, CEO, AnalystFinder.com
That worked Art!!!!!
Thank you so much for your help - awesome job!!!
God bless, best regards, and take care,
Jadallah
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.