topic Re: Subtract 30 days from the last date in SAS Data Management

Subtract 30 days from the last date

AZIQ1 — Tue, 10 Sep 2019 14:59:44 GMT

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

Re: Subtract 30 days from the last date

PaigeMiller — Tue, 10 Sep 2019 15:01:27 GMT

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

Re: Subtract 30 days from the last date

novinosrin — Tue, 10 Sep 2019 15:15:11 GMT

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;

Re: Subtract 30 days from the last date

AZIQ1 — Tue, 10 Sep 2019 15:21:32 GMT

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;

Re: Subtract 30 days from the last date

Jagadishkatam — Tue, 10 Sep 2019 15:21:55 GMT

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;

Re: Subtract 30 days from the last date

PaigeMiller — Tue, 10 Sep 2019 15:25:41 GMT

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

Re: Subtract 30 days from the last date

AZIQ1 — Tue, 10 Sep 2019 15:30:32 GMT

Thank you so much

Re: Subtract 30 days from the last date

novinosrin — Tue, 10 Sep 2019 15:31:26 GMT

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

Re: Subtract 30 days from the last date

AZIQ1 — Tue, 10 Sep 2019 15:32:27 GMT

In some instances for testing purpose

Re: Subtract 30 days from the last date

PaigeMiller — Tue, 10 Sep 2019 15:33:37 GMT

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