Try something like this. Tough without seeing the data/log.
proc sql;
create table bb as
select DATEPART(TEST_DATE) AS TESTED LABEL = 'TESTED',
TESC_CODE,
TEST_DATE,
TODAY() AS AA ,
DATEPART(TEST_DATE) as Date format=date9.
from stg.sortest;
where calculated Date >= intnx('year', today() ,-5,'sameday');
quit;
In your original code I see a few issues:
1. Today() - datepart(Test_Date) will give you how many days between today and whatever test_date is. That will not be a year. It will be a numeric value.
2. where Year < '1825' won't work for two reasons. One Year is currently numeric (from above), so a test against a character will probably cause an error. Also, Year is a calculated column, so you need to use the calculated keyword.
The INTNX function increments a date. I specify to use today's date, use year increments, and go back 5 years from today's date exactly. You an change 'sameday' to a variety of different methods. See INTNX Function
I also wrote some code with fake data that uses different date functions for you to see:
data test;
Datetime='17OCT2011:12:5'DT;
output;
Datetime='17OCT2012:12:5'DT;
output;
Datetime='17OCT2013:12:5'DT;
output;
Datetime='17OCT2014:12:5'DT;
output;
Datetime='17OCT2015:12:5'DT;
output;
Datetime='17OCT2016:12:5'DT;
output;
Datetime='17OCT2017:12:5'DT;
output;
Datetime='17OCT2018:12:5'DT;
output;
Datetime='17OCT2019:12:5'DT;
output;
format DateTime datetime.;
run;
proc sql;
create table Final as
select DateTime,
datepart(DateTime) as Date format=date9.,
day(calculated Date) as Day,
month(calculated Date) as Month,
year(calculated Date) as Year,
intnx('year', today(),-5,'sameday') as FiveYearsAgoFromToday format=date9.
from test
where calculated Date >= intnx('year', today(),-5,'sameday');
quit;
... View more
