Choosing a Date Range in Macro Code

[BCNAV]

I am trying to fix up a SAS EG project that I have "inherited"

There is a code node that is necessary at the start to assign dates.  The user will open the code node and enter the number of days back in time they wish to use.  So the following is used:

%let numdays=8;

The code that is there now identifies the start and end date for variable selection later (based on date).  So two variables are created; a start date and end date. The current code, which I believe is wrong is:

%let startdt=datetime()-(&numdays*(60*60*24)) + (170)*60;

%let enddt=(today());

First off, I believe the last line should be:

%let enddt=%sysfunc(today());

So this leaves the startdt to fix. I am not sure at all what the code should be.  All I need is for the start date to be 8 days ago (or whatever the number) from today. In any case, you will notice that there are no sysevalf or sysfunc in the original code, so it is wrong anyway.  Thanks. I have no idea why the 170*60 is there and cannot ask the person who wrote it why it is in place.

[PaigeMiller]

%let numdays=8;
%let now=%sysfunc(datetime());
%let startdt=%sysevalf(&now-&numdays*60*60*24 + 170*60);

This does obtain the expected value of &startdt. However ... I don't think this is what you want. Your other statement to create &enddt creates a value that is a SAS DATE value. The above value of &startdt will be a SAS DATETIME value.

So, the important question is: do you REALLY want a SAS date value for &enddt and a datetime value for &startdt? That seems illogical to me, but may in certain situations its what you need. What are you going to do with these two macro variables?

[ballardw]

---

@BCNAV wrote:

I am trying to fix up a SAS EG project that I have "inherited"

%let enddt=(today());

First off, I believe the last line should be:

%let enddt=%sysfunc(today());

---

You would have to show or examine the code that uses the macro variable to determine correctness. I admit that it looks odd but if the use looks like

%let enddt=(today());
data example;
   date = &enddt.;
   format date mmddyy10.;
run;

or is embedded in places where the call (today()) makes sense then it is okay. In my example your suggestion would also work. But I would closely examine the code that uses &enddt for other types of use.

[BCNAV]

Sorry, I did not say what the macro variables are used for. In my original post, the ones to use are:

startdt and enddt

They are use later:

PROC SQL;
   CREATE TABLE EGTASK.QUERY_FOR_MSG_FLIGHT_SURVEI_0003 AS
   SELECT t1.ACID,
          t1.ACID_NORMALIZED,
          t1.FLT_DEPARTURE_AD_CODE,
          t1.FLT_DESTINATION_AD_CODE,
          t1.TRK_CODE,
          t1.ACFT_TYPE_CODE,
          t1.CJS_CODE,
          t1.ACC_CJS_IND,
          t2.MSG_STATE_CODE,
          t2.MSG_BIZ_UID,
          t2.MSG_DATE,
          t2.MSG_SOURCE_SITE_CODE
      FROM FDW_FLT.V_MESSAGE_SURVEILLANCE t1
           INNER JOIN FDW_FLT.V_MESSAGE t2 ON (t1.MSG_BIZ_UID = t2.MSG_BIZ_UID)
      WHERE t2.MSG_DATE BETWEEN &startdt AND &enddt;
QUIT;

So they are used in WHERE t2.MSG_DATE BETWEEN &startdt AND &enddt;

Note that t2.MSG_DATE is DATE9. format

thanks

[PaigeMiller]

So this won't work. You can't use a SAS date value for &enddt and a SAS datetime value for &startdt.

What is the variable MSG_DATE? Is it a date, or a date/time?

[BCNAV]

Actually, there is another variable I can used....MSG_DATETIME  It is datetime26.7

I think I could then use &now and &startdt in:

PROC SQL;
   CREATE TABLE EGTASK.QUERY_FOR_MSG_FLIGHT_SURVEI_0003 AS
   SELECT t1.ACID,
          t1.ACID_NORMALIZED,
          t1.FLT_DEPARTURE_AD_CODE,
          t1.FLT_DESTINATION_AD_CODE,
          t1.TRK_CODE,
          t1.ACFT_TYPE_CODE,
          t1.CJS_CODE,
          t1.ACC_CJS_IND,
          t2.MSG_STATE_CODE,
          t2.MSG_BIZ_UID,
          t2.MSG_DATE,
          t2.MSG_SOURCE_SITE_CODE
      FROM FDW_FLT.V_MESSAGE_SURVEILLANCE t1
           INNER JOIN FDW_FLT.V_MESSAGE t2 ON (t1.MSG_BIZ_UID = t2.MSG_BIZ_UID)
      WHERE t2.MSG_DATETIME BETWEEN &startdt AND &now;
QUIT;

I think so?

[PaigeMiller]

Why don't you run the code and find out if it works?

[BCNAV]

It takes about 3 hours to run...so I thought I'd ask if my logic was ok first.....I'll run it

[ballardw]

---

@BCNAV wrote:

It takes about 3 hours to run...so I thought I'd ask if my logic was ok first.....I'll run it

---

Create severely reduced data sets from the ones you would use, may be only a 100 records or so each.

reducing data early will usually speed things up

instead of

INNER JOIN FDW_FLT.V_MESSAGE t2 ON (t1.MSG_BIZ_UID = t2.MSG_BIZ_UID)
      WHERE t2.MSG_DATETIME BETWEEN &startdt AND &now;

consider

inner join (select MSG_STATE_CODE,MSG_BIZ_UID,MSG_DATE,MSG_SOURCE_SITE_CODE
            from fdw_flt.v_message
            where msg_datetime between &startdt and &now ) as t2

to reduce the number records from fdw_flt.v_message early and the number of variables in the work space.