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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.