BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AZIQ1
Quartz | Level 8

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:

 

IDDate
11/1/2019
21/31/2019
32/3/2019
42/4/2019
52/5/2019
62/28/2019
73/1/2019
83/1/2019
93/2/2019
103/3/2019
113/5/2019
123/31/2019

 

 

want

 

IDDate
11/1/2019
21/31/2019
32/3/2019
42/4/2019
52/5/2019
62/28/2019
73/1/2019
83/1/2019

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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;

View solution in original post

9 REPLIES 9
PaigeMiller
Diamond | Level 26

@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;
--
Paige Miller
novinosrin
Tourmaline | Level 20

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
Quartz | Level 8
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
Diamond | Level 26

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

 

--
Paige Miller
AZIQ1
Quartz | Level 8
Thank you so much
novinosrin
Tourmaline | Level 20

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
Quartz | Level 8
In some instances for testing purpose
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
Jagadishkatam
Amethyst | Level 16

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;
Thanks,
Jag

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 9 replies
  • 15363 views
  • 4 likes
  • 4 in conversation