Need help with INTNX

[ILeskow]

Hi, I am trying to do the following to pass in the system date and subtract the date so the StartDate will read 09MAR2009 and the EndDate will be 15MAR2009. I'm not sure if I am doing this correctly. This will allow me to run the code automatically instead of hard coding a date each time. Any help is appreciated!


%LET StartDate =%sysfunc(intnx(week.2, "&SYSDATE"D, 0), DATE9.);
%LET EndDate =%sysfunc(intnx(week.7, "&SYSDATE"D, 0), DATE9.);

PROC SQL;
CREATE TABLE TD_Mature AS
SELECT distinct t.contract_no AS Matured_TD
FROM ptware.term_deposits t
WHERE t.mature_date between '&StartDate' and '&EndDate'
AND t.cease_date is not null
AND t.mature_date = t.cease_date;
quit;

[sbb]

Setting OPTIONS SOURCE SOURCE2 MACROGEN SYMBOLGEN MLOGIC; would likely help reveal SAS behavior when using your macro variables in your PROC SQL code. The macro variable string, when coded within single quote marks, will not be resolved, as compared to coding the macro variable string inside double-quote marks -- this is a SAS standard feature.

Scott Barry
SBBWorks, Inc.

SAS DOC links from the SAS support website http://support.sas com/

Introduction to Macro Variables
http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/a002293823.htm


Displaying Macro Variable Values
http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/a001071910.htm


SAS Conference Paper on the topic:

Add SAS® Macros to Your Programming Skills: Achieve More, Write Less Code
Peter Crawford, Crawford Software Consultancy Ltd, UK
http://www2.sas.com/proceedings/forum2008/175-2008.pdf

[LinusH]

I'm not sure what kind of interval you are trying to create, but if you intend to always have Monday to Sunday in the current week, I think you misunderstood the use of the week.X interval. I think that you should stick with week.2, and use the BEGINNING and END with the intnx function:

%LET StartDate =%sysfunc(intnx(week.2, "&SYSDATE"D, 0, beginning), DATE9.);
%LET EndDate =%sysfunc(intnx(week.2, "&SYSDATE"D, 0, end), DATE9.);
%put &startdate &enddate;
09MAR2009 15MAR2009

/Linus