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
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 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;
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 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;
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
@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.
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;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.