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?
SAS dates (numeric variable with one of the date formats attached) are counts of days, so you can use simple subtractions.
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.
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
@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.
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 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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: