define date range and generate date increments

[Jaaa]

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

Obs rdate yq quarter

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;

[Astounding]

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?

[Jaaa]

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

rdate yq endfyr begfyr

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! 

[Astounding · Accepted solution]

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'); 

[Jaaa]

Brilliant! You are right!! Thank you a lot! 

[Jaaa]

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!!! 🙂  

[Astounding]

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.

[Jaaa]

very well understood! Thank you very much for the explanation!