topic Re: Pull only last 5 year from today in SAS Programming

Pull only last 5 year from today

lerdem — Wed, 28 Aug 2019 14:22:06 GMT

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;

Re: Pull only last 5 year from today

PaigeMiller — Wed, 28 Aug 2019 14:19:10 GMT

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.

Re: Pull only last 5 year from today

lerdem — Wed, 28 Aug 2019 14:41:46 GMT

Hello,

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

Thanks

Re: Pull only last 5 year from today

PaigeMiller — Wed, 28 Aug 2019 14:48:00 GMT

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-data-AKA-generate/tac-p/580061

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

Re: Pull only last 5 year from today

Panagiotis — Wed, 28 Aug 2019 14:51:00 GMT

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;