Subtract 30 days from the last date

[AZIQ1]

Hi,

Need some help

I want to exclude/subtract 30 days from the last day I select

Lets say I have selected date from Jan 2019 through March 2019 , I want to subtract 30 days from the date time frame that I selected, so it should exclude data from March 2nd through March 31st. The date range might change so it should be dynamic like every time i select a date range SAS should subtract 30 days from the range based on the last month I selected.

Data Have:

ID	Date
1	1/1/2019
2	1/31/2019
3	2/3/2019
4	2/4/2019
5	2/5/2019
6	2/28/2019
7	3/1/2019
8	3/1/2019
9	3/2/2019
10	3/3/2019
11	3/5/2019
12	3/31/2019

want

ID	Date
1	1/1/2019
2	1/31/2019
3	2/3/2019
4	2/4/2019
5	2/5/2019
6	2/28/2019
7	3/1/2019
8	3/1/2019

Thanks

[PaigeMiller]

---

@AZIQ1 wrote:

Hi,

Need some help

I want to exclude/subtract 30 days from the last day I select

Lets say I have selected date from Jan 2019 through March 2019 , I want to subtract 30 days from the date time frame that I selected, so it should exclude data from March 2nd through March 31st. The date range might change so it should be dynamic like every time i select a date range SAS should subtract 30 days from the range based on the last month I selected.

Data Have:

ID	Date
1	1/1/2019
2	1/31/2019
3	2/3/2019
4	2/4/2019
5	2/5/2019
6	2/28/2019
7	3/1/2019
8	3/1/2019
9	3/2/2019
10	3/3/2019
11	3/5/2019
12	3/31/2019

want

ID	Date
1	1/1/2019
2	1/31/2019
3	2/3/2019
4	2/4/2019
5	2/5/2019
6	2/28/2019
7	3/1/2019
8	3/1/2019

Thanks

---

data last;
    set have end=eof;
    if eof then output;
run;
data want;
    if _n_=1 then set last(rename=(date=last_date));
    set have;
    if date<(last_date-30);
run;

[novinosrin · Accepted solution]

Hello @AZIQ1   SQL

data have;
input ID	Date :mmddyy10.;
format date mmddyy10.;
cards;
1	1/1/2019
2	1/31/2019
3	2/3/2019
4	2/4/2019
5	2/5/2019
6	2/28/2019
7	3/1/2019
8	3/1/2019
9	3/2/2019
10	3/3/2019
11	3/5/2019
12	3/31/2019
;


proc sql;
create table want as
select *
from have
having date<=max(date)-30;
quit;

[AZIQ1]

If i want to specify a date in the formula e.g instead of max date lets can I use something like this:
proc sql;
create table want as
select *
from have
having date<=max(03/31/2019)-30;
quit;

[PaigeMiller]

---

@AZIQ1 wrote:
If i want to specify a date in the formula e.g instead of max date lets can I use something like this:
proc sql;
create table want as
select *
from have
having date<=max(03/31/2019)-30;
quit;

---

To specify a max date, you would need to specify the date as '31MAR19'd (that exact format, or the equivalent '31MAR2019'd). Such as:

data want;
    set have;
    if date<('31MAR19'd-30);
run;

[AZIQ1]

Thank you so much

[novinosrin]

Why do you want to hard code? 

That defeats the purpose of programming, though @PaigeMiller  showed you how to write a SAS date constant i.e 'ddmmmyyyy'd

[AZIQ1]

In some instances for testing purpose

[PaigeMiller]

---

@novinosrin wrote:

Why do you want to hard code? 

---

Because we always advise people to get working SAS code without macros and without macro variables, before trying to make it work with macros and macro variables.

[Jagadishkatam]

Alternatively please try

data have;
input ID Date:mmddyy10.;
format date date9.;
cards;
1  1/1/2019
2  1/31/2019
3  2/3/2019
4  2/4/2019
5  2/5/2019
6  2/28/2019
7  3/1/2019
8  3/1/2019
9  3/2/2019
10 3/3/2019
11 3/5/2019
12 3/31/2019
;

data want;
do until(eof);
set have end=eof;
by id;
lastdate=date;
format lastdate date9.;
if eof;
end;
do until(eof2);
set have end=eof2;
if date<=(lastdate-30) then output;
end;
run;