topic Re: Selecting subset of data relative to today's date in SAS Data Management

Selecting subset of data relative to today's date

Jordan88 — Wed, 07 Dec 2016 18:10:56 GMT

Hi All,

So I have what I feel like should be a simple task, but I just can't figure it out.

I am working with very large dataset stores on Oracle servers and need to use the data for a variety of Tableau dashboards. The datasets are so large that working off a live feed from the Oracle servers isn't feasible, so we have to create subsets of the data and either connect live to those, or create extracts.

What I would like to do is set it up so that every time my code runs it selects the last 1 year of data, relative to the date that the code is run. Right now it is set up to pull anything over a pre-specified date, but obviously as the date gets further away the larger the dataset becomes and I don't want to have to worry about going in and changing it overy so often.

Here is the code I tried:

data SUBSET; set ORIGINAL;
   where EVENT_REPORTED_DATETIME >= (%sysfunc(DATETIME(),datetime20.) -365);
run;

It results in the following error:

NOTE: Line generated by the macro function "SYSFUNC".
1 07DEC2016:11:10:26
 -------
 22
 76
ERROR: Syntax error while parsing WHERE clause.
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, ), *, **, +, -, /, <, <=,
 <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, GE, GT, IN, IS, LE, LIKE, LT, NE, NOT,
 NOTIN, OR, ^, ^=, |, ||, ~, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

I tried a few other similar methods, but they either returned no observations or all observations.

Re: Selecting subset of data relative to today's date

TheShark — Wed, 07 Dec 2016 18:21:53 GMT

I would just use TODAY instead of datetime. I believe the time portion is throwing a monkey wrench into the equation.

data SUBSET; set ORIGINAL;
   where EVENT_REPORTED_DATETIME >= (%sysfunc(today())-365);
run;

Re: Selecting subset of data relative to today's date

Jordan88 — Wed, 07 Dec 2016 18:56:35 GMT

Alright, figured it out! Your comment kind of got me going in the right direction.

This is actually what I had originally tried, but it returns all entries, not just those within the last year.

I thought it might be due to the format (it is DATETIME20.) so tried DATETIME instead.

If enter

$put %sysfunc(today());

I get the value 20795. Which is the number of days past Jan 1, 1960.

If I put

%put sysfunc(datetime())

I get 1796730229.087 (or something similar), which is the number of SECONDS past that date. Also, when I get the minimum datetime value in my dataset, it has a value of 1785628860.

So when I am substracting 365 from 20795 (today()), it ends up comparing my datetime (which is seconds) to a value of 20430 (which is days), so my datetime value is always larger. What I had to do was keep DATETIME() in the equation, drop the "datetime20." format (that was throwing a wrench in the works) and subtract the number of SECONDS IN A YEAR.

SO the final equation to get it work work looks like this

data SUBSET; set ORIGINAL;
   where EVENT_REPORTED_DATETIME >= (%sysfunc(datetime())-31536000);
run;

Who knew.

Re: Selecting subset of data relative to today's date

slchen — Wed, 07 Dec 2016 19:08:36 GMT

Your EVENT_REPORTED_DATETIME is datetime format, you could try to use datetime():

where EVENT_REPORTED_DATETIME>= intnx('dtmonth',datetime(),-12,'same');

Re: Selecting subset of data relative to today's date

Jordan88 — Wed, 07 Dec 2016 19:22:22 GMT

Thanks! This works as well as the solution I ended up figuring out.