Solved
Contributor
Posts: 20

# define date range and generate date increments

[ Edited ]

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;``````

Accepted Solutions
Solution
‎11-08-2017 03:03 PM
Super User
Posts: 6,624

## Re: define date range and generate date increments

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

All Replies
Super User
Posts: 6,624

## Re: define date range and generate date increments

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?

Contributor
Posts: 20

## Re: define date range and generate date increments

[ Edited ]

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!

Solution
‎11-08-2017 03:03 PM
Super User
Posts: 6,624

## Re: define date range and generate date increments

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

Contributor
Posts: 20

## Re: define date range and generate date increments

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

Contributor
Posts: 20

## Re: define date range and generate date increments

[ Edited ]

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

Super User
Posts: 6,624

## Re: define date range and generate date increments

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.

Contributor
Posts: 20