SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Selecting subset of data relative to today's date

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

Selecting subset of data relative to today's date

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. 

 

 


Accepted Solutions
Solution
‎12-07-2016 02:23 PM
Super Contributor
Posts: 275

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

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

 

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

View solution in original post


All Replies
Contributor
Posts: 35

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

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;
Contributor
Posts: 22

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

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. 

Solution
‎12-07-2016 02:23 PM
Super Contributor
Posts: 275

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

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

 

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

Contributor
Posts: 22

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

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 297 views
  • 2 likes
  • 3 in conversation