Intnx function

[ScottBass]

---

@LL5 wrote:
Thanks everyone so much for all the advices!! I tried the below approach and it worked.
%LET YEAR = 2016;
%LET DATE = '31AUG2016'D;
%LET DATE1 = %sysfunc(INTNX(MONTH,&DATE,-1,E));
%LET DATE2 = %sysfunc(INTNX(QTR,&DATE,-1,E));
%LET DATE3 = %sysfunc(INTNX(QTR,&DATE,-2,E));
%LET DATE4 = %sysfunc(INTNX(MONTH,&DATE,-12,E));
%LET DATE5 = %sysfunc(MDY(3,31,&YEAR-1));
%LET DATE6 = %sysfunc(INTNX(MONTH,&DATE5,-12,E));

DATA TEST;
SET WAREHOUSE;
WHERE AS_OF_DATE IN (&DATE, &DATE1,&DATE2, &DATE3, &DATE4, &DATE5, &DATE6);
RUN;

---

As often with SAS (and Perl), "there is more than one way to do it" (tm)

Now that this has evolved to six macro variables (and perhaps more in the future?), perhaps a non-macro approach could be useful?

The "real" problem here is selecting records matching key dates.  The macro code was just to derive those dates.  This can be done in many ways without the use of macro:

First create your desired dates (however you choose to do so):

data dates;
   start='31AUG2016'd;  * or today(), or whatever seed value you need ;
   date=intnx('month',start,0,'E');output;
   date=intnx('qtr',start,-2,'E');output;
   date=mdy(31,3,2016-1);output;
   date=mdy(1,4,2016-2)-1;output;
   * etc, etc, ;  * create a dataset containing the dates you want ;
run;

Then, you can subset your data with:

* merge and IN=  (but this requires the source and lookup data to be sorted)

* proc sql inner join (but this will sort the source data under the covers)

* proc sql nested query  (proc sql; create table test as select * from warehouse where date in (select date from dates) )

* create a format from your dates, then:  (data test; set warehouse; if not missing(put(date,mydatefmt.)); run; )

* use a hash object for your dates, and check the return code from the hash.lookup() function

* create an indexed dataset for your dates, and use a lookup ("double set statement"), and check _iorc_

None of these approaches would use macro, and might be easier to modify if your needs change in the future.

If your WAREHOUSE dataset is large you want to avoid sorting.  Use the _method option on proc sql to see if it sorted your source table or not.

Hope this helps...

[LL5]

Thanks ScottBass for letting me know the other approaches to accomplish this. Since the data in warehouse is very large which contains lots of observations and variables, I used Proc Sql followed by lots of Proc Tabulate statements to get the end result. I will keep in mind for all the different approaches that you mentioned above and may come up with additional questions in the future. Thanks 🙂

[LL5]

And also, I used this in Proc Sql, it worked as well.