Pull only last 5 year from today

[lerdem]

I am new with SAS

Trying to pull only last 5 year data from today. Comparing with test_date. This is my code, but doesn't work

          proc sql;
                      create table bb as
                     select DATEPART(TEST_DATE) AS TESTED LABEL = 'TESTED',
                     TESC_CODE,
                     TEST_DATE,
                      TODAY() AS AA ,
                      TODAY() - DATEPART(TEST_DATE) as year

                    from stg.sortest;
                      where year < '1825';

                          quit;

[PaigeMiller]

I'm guessing you want this 

where calculated year < 1825

If that's not it, then please show the complete SAS log of this step (not just the error message) by clicking on the {i} icon and pasting the log into the window that appears.

In the future, would you please be so kind as to paste your SAS Code in the window that appears when you click on the "running man" icon. This makes the appearance of the code much more readable. Thanks.

[lerdem]

Hello,

I corrected how it looks the code.  The new addition is not working. No error come out, didn't eliminate anything.

Thanks 

[PaigeMiller]

You need to provide sample data (not necessarily the entire data set) by following these instructions: 

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

Also, please show us the LOG anyway, by clicking on the {i} icon and pasting the log into the window that appears.

[Panagiotis · Accepted solution]

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;