To reduce days from a date in proc sql

[NK29]

Hi,

I have been using this to get data between system date and 5 days through sql statement

select *

from Table

where date BETWEEN SYSDATE -&days AND SYSDATE

days is initialized to 5

Now,

Instead of SYSDATE, i need to use my own date already preset in dataset called FL_DT . I am passing FL_DT as a macro variable

select *

from Table

where date BETWEEN &FL_DT -&days AND &FL_DT

It is in Date9 format and i need to reduce 5 days from it in the same sql format as above. Could it be possible?

[Kurt_Bremser]

SAS dates (numeric variable with one of the date formats attached) are counts of days, so you can use simple subtractions.

[NK29]

I am passing the date as a macro variable. Just like sysdate

[Kurt_Bremser]

Since you want to use your date-stored-in-a-macro-variable for comparisons, I strongly advise to store the raw numerical value (eg 21383 for 2018-07-18) in the macro variable, so you don't have to use "&mydate."d all the time. As said, it's a simple subtraction.

[s_lassen]

You just need to use the date constant format, e.g.:

select *
from Table
where date BETWEEN "&FL_DT"d -&days AND "&FL_DT"d

I much prefer that to the solution suggested by @Kurt_Bremser, which would make the code much harder to read.

The alternative is to assign the date constant format to the variable (or another variable, if you prefer that) first:

%let FL_DT="&FT_DT"d;
select *
from Table
where date BETWEEN &FL_DT -&days AND &FL_DT

[Kurt_Bremser]

---

@s_lassen wrote:

I much prefer that to the solution suggested by @Kurt_Bremser, which would make the code much harder to read.

---

I beg to differ. I don't see where

proc sql;
create table want as
select *
from have
where date between &FL_DT -&days and &FL_DT;
quit;

is much harder to read than

proc sql;
create table want as
select *
from have
where date between "&FL_DT"d -&days and "&FL_DT"d;
quit;

Since the date9 format is not used by any software outside of SAS (that I know of), there will always be a step in your SAS code that converts an incoming date to something usable in SAS. Whether you use date9. or best. there when creating the macro variable makes no difference.

The ONLY real difference that happens is the display of values in the log when you use the macro variable in a where condition in a data step. That won't make much of a difference when debugging, as the human-readable form of the date can be made visible in the "import" step mentioned above.

[s_lassen]

What you suggested was this: "I strongly advise to store the raw numerical value (eg 21383 for 2018-07-18) in the macro variable, so you don't have to use "&mydate."d all the time."

That is what I thought was a bad idea, as you cannot easily see what a number means. 21384 is not as easy to understand when debugging as "19JUL2018"d.

[NK29]

Hi when I'm reading date into a macro variable from my dataset, it is in
format 02JUN2018:00:00:00. P. S I am using proc sql to read it into a macro
variable seperated by comma

[Kurt_Bremser]

---

@NK29 wrote:
Hi when I'm reading date into a macro variable from my dataset, it is in
format 02JUN2018:00:00:00. P. S I am using proc sql to read it into a macro
variable seperated by comma

---

So you do not have dates, but datetimes. Use the datepart() function to extract the date.

And in your initial post, you CAN NOT use a macro variable that contains a list of dates.