topic Re: To reduce days from a date in proc sql in SAS Programming

To reduce days from a date in proc sql

NK29 — Wed, 18 Jul 2018 08:55:19 GMT

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?

Re: To reduce days from a date in proc sql

Kurt_Bremser — Wed, 18 Jul 2018 08:45:17 GMT

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

Re: To reduce days from a date in proc sql

NK29 — Wed, 18 Jul 2018 09:09:42 GMT

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

Re: To reduce days from a date in proc sql

Kurt_Bremser — Wed, 18 Jul 2018 09:19:30 GMT

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.

Re: To reduce days from a date in proc sql

s_lassen — Wed, 18 Jul 2018 10:07:58 GMT

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

Re: To reduce days from a date in proc sql

Kurt_Bremser — Wed, 18 Jul 2018 11:22:19 GMT

@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.

Re: To reduce days from a date in proc sql

s_lassen — Thu, 19 Jul 2018 08:15:15 GMT

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.

Re: To reduce days from a date in proc sql

NK29 — Thu, 19 Jul 2018 08:23:42 GMT

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

Re: To reduce days from a date in proc sql

Kurt_Bremser — Thu, 19 Jul 2018 08:47:38 GMT

@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.