BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jaaa
Fluorite | Level 6

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

7 REPLIES 7
Astounding
PROC Star

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
Fluorite | Level 6

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! 

Astounding
PROC Star

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
Fluorite | Level 6

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

Jaaa
Fluorite | Level 6

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
PROC Star

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
Fluorite | Level 6

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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