Hello SAS professionals,
I would like to define my obtained dataset with a date range from "1980Q1" to "2016Q4" and then generate a beginning date by lagging 3 quarters at the reporting date. However, an empty dataset is produced by the following codes so there must be somewhere got wrong which I didn't notice. Thus, are there any professionals who can help with my coding?
My partial dataset is also attached here for your review. Many thanks in advance!!! 🙂
SAS Output
19800331 | 198001 | 1980Q1 |
19800331 | 198001 | 1980Q1 |
19800331 | 198001 | 1980Q1 |
19800331 | 198001 | 1980Q1 |
19800331 | 198001 | 1980Q1 |
19800331 | 198001 | 1980Q1 |
19800331 | 198001 | 1980Q1 |
19800331 | 198001 | 1980Q1 |
19800331 | 198001 | 1980Q1 |
19800331 | 198001 | 1980Q1 |
%let fq1= 198001;
%let fq2= 201604;
* Make extract from Thomason Reuters Stock Ownership file;
data compx2;
set MMIO_CUSIP;
where quarter between &fq1 and &fq2;
* create begin and end dates for fiscal year;
format endfyr begfyr date9.;
endfyr= rdate;
begfyr= intnx('QTR',endfyr,-3,'beg'); /* intnx(interval, from, n, 'aligment') */
run;
For that, you need to change the INTNX interval. INTNX returns the first day of the quarter. You want to go back 9 months, not 3 quarters. So this should get you there:
begfyr= intnx('month',endfyr,-9,'end');
Based on the posted data, you are using the wrong variable in the WHERE statement. The values of YQ (not the values of QUARTER) would fall in the range defined by your macro variables.
If that's not the problem, you will need to supply more information about your data. Which variables are character and which are numeric? Which have formats applied? Is RDATE really a date, or is it an eight-digit integer?
I know what you mean. When I tried "yq" with WHERE clause, it produces something not what I want.
They are all numerical date variables, format is DATE9. What I truly want is to have lagged 3 quarters dates based on the reporting date RDATE instead of lagging 12 months, here is the wrong result of using "yq":
SAS Output
19800331 | 198001 | 31MAR1980 | 01APR1979 |
19800331 | 198001 | 31MAR1980 | 01APR1979 |
19800331 | 198001 | 31MAR1980 | 01APR1979 |
19800331 | 198001 | 31MAR1980 | 01APR1979 |
19800331 | 198001 | 31MAR1980 | 01APR1979 |
19800331 | 198001 | 31MAR1980 | 01APR1979 |
19800331 | 198001 | 31MAR1980 | 01APR1979 |
19800331 | 198001 | 31MAR1980 | 01APR1979 |
19800331 | 198001 | 31MAR1980 | 01APR1979 |
19800331 | 198001 | 31MAR1980 | 01APR1979 |
I'd like to have, for example, endfyr is 31MAR1980, the begfyr should be 30JUN1979. Do you possibly know how to achieve that? Many thanks in advance!
For that, you need to change the INTNX interval. INTNX returns the first day of the quarter. You want to go back 9 months, not 3 quarters. So this should get you there:
begfyr= intnx('month',endfyr,-9,'end');
Brilliant! You are right!! Thank you a lot!
Can I ask you a quick question? I've seen some posts that using INTNX function with "QTR" for returning quarter date, why can't we use that?
Many thanks indeed!!! 🙂
You can. But there are only four "first days of a quarter" per year: January 1, April 1, July 1, and October 1. If you want the first day of a time period, it's not possible for INTNX with QTR to return any other value.
very well understood! Thank you very much for the explanation!
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: