DATA Step, Macro, Functions and more

define date range and generate date increments

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

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

 

SAS Output

Obs rdate yq quarter
198003311980011980Q1
198003311980011980Q1
198003311980011980Q1
198003311980011980Q1
198003311980011980Q1
198003311980011980Q1
198003311980011980Q1
198003311980011980Q1
198003311980011980Q1
198003311980011980Q1

 

%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
2 weeks ago
Super User
Posts: 5,509

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

View solution in original post


All Replies
Super User
Posts: 5,509

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 ]
Posted in reply to Astounding

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
1980033119800131MAR198001APR1979
1980033119800131MAR198001APR1979
1980033119800131MAR198001APR1979
1980033119800131MAR198001APR1979
1980033119800131MAR198001APR1979
1980033119800131MAR198001APR1979
1980033119800131MAR198001APR1979
1980033119800131MAR198001APR1979
1980033119800131MAR198001APR1979
1980033119800131MAR198001APR1979

 

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
2 weeks ago
Super User
Posts: 5,509

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

Posted in reply to Astounding

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

Contributor
Posts: 20

Re: define date range and generate date increments

[ Edited ]
Posted in reply to Astounding

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

Super User
Posts: 5,509

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

Re: define date range and generate date increments

Posted in reply to Astounding

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 136 views
  • 0 likes
  • 2 in conversation