Macro auto populate run date

[vomer]

Hi guys,

So I have a sas program that is setup to run weekly (on sundays). The way the code is setup...I need to change the date in the following macro every week which gets pretty tedious

%let et_mth = '29FEB2012:23:59:59'Dt;

Is there any way I can alter this macro so that it picks up the date and time as shown above automatically from the day the code is run? Please provide new code if possible

Thanks!

[Haikuo]

I am not sure if I understand what you need, But the following will pick up the SAS session start time aumatically and put it in a macro variable.

%let runtime= "&sysdate9.:&systime"Dt;

%put &runtime;

Regards,

Haikuo

[vomer]

The value in the macro I posted is used in the sas program to limit the data set by that date. Basically I am looking for a way for that value to be auto populated when I run the code so it can be used in the program.

Would what you posted work for this? I don't think I need the "put" part as I do not need to print it....?

Also would this be correct too?:

%let runtime= "&sysdate9.:23:59:59"Dt;

[Haikuo]

1.  %let runtime= "&sysdate9.:23:59:59"Dt;

Yes, the syntax is good, use %put to check it.

2. "I don't think I need the "put" part"

No, you don't need %put, which was showing the contents of your macro variable.

3.  "The value in the macro I posted is used in the sas program to limit the data set by that date."

You need to reveal the rules that being used to generate this date, and we can figure out a way to put it into that macro varaible for downstream usage.

[vomer]

this is how I use that macro right now :

if sample_date > & runtime. then delete;

would it work?

[ballardw]

Is sample_date a DATE variable or a DATETIME variable.

Your last example code has a space between the "&" and "runtime", so probably won't work. Also, I suggest being very careful about the use of "." after your macro variable names and use it only when you intend to concatenate with something following.

What is the rule for seting RUNTIME? The example you picked on 29 Feb is such an oddball it could be a lot of things.

You may be able to use INTNX function without a macro variable at all unless it is used for something else.

Suppose I only want to exclue all records where your sample date is after the previous sunday of the date the program is run. This assumes that sample_date is a date value.

if sample_date > intnx('WEEK', date(), -1,'B') then delete;

or

if datepart(sample_date) > intnx('WEEK', date(), -1,'B') then delete;

The 'B' indicates begining of week. If you have adjusted your system to use a different day of the week as day 1 adjustements are needed.

[Patrick]

When setting up jobs run in batch on a regular basis and the rundate of the job is used for some data selection then I always try to implement robust enough for "what if" scenarios.

What if the job gets delayed or fails and only runs the next day? What if the data selection step of a job runs exactly over midnight? What data will be selected?

Below some code demonstrating how you could deal with such cases.

The sample code returns a macro variable containing a date or datetime string which is aligned to the previous Wednesday relative to the rundate. That's just to give you the idea and you would have to tweak the code so that it serves your purpose.

For data selection something like below would work:

data want;

     set have(where=(date>"shift_DT"d));

...

or

data want;

   set have(where=(datetime>"shift_DTTM"dt));

...

options ls=180;

/* for datetime values */
data _null_;
  format StartDate ShiftDate dtwkdatx.;
  do StartDate=datetime()-10*86400+1000 to datetime()+1000 by 86400;
    ShiftDate=intnx('DTweek0.5',StartDate,-1,'e');
    put StartDate= StartDate tod9.  @60 ShiftDate= ShiftDate tod9.;
  end;
run;

%let shift_DTTM=%sysfunc(datetime());
%let shift_DTTM=%sysfunc(intnx(DTweek0.5,&shift_DTTM,-1,e));
%let shift_DTTM=%sysfunc(putn(&shift_DTTM,datetime21.));

%put shift_DTTM = &shift_DTTM;

/* for date values */
data _null_;
  format StartDate ShiftDate weekdatx.;
  do StartDate=date()-10 to date();
    ShiftDate=intnx('week0.5',StartDate,-1,'e');
    put StartDate= @60 ShiftDate= ;
  end;
run;

%let shift_DT=%sysfunc(date());
%let shift_DT=%sysfunc(intnx(week0.5,&shift_DT,-1,e));
%let shift_DT=%sysfunc(putn(&shift_DT,date9.));

%put shift_DT = &shift_DT;

[Tom]

Make sure that your dataset variable is a datetime value.  From the name it looks like it should just be a date value.

If that is the case you could just code:

if sample_date > "&date9"d then delete;

or

if sample_date <= "&date9"d ;

Also how are you going to have data from future dates in your database?