BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jordan88
Obsidian | Level 7

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. 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
slchen
Lapis Lazuli | Level 10

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

4 REPLIES 4
TheShark
Obsidian | Level 7

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;
Jordan88
Obsidian | Level 7

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. 

slchen
Lapis Lazuli | Level 10

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

 

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

Jordan88
Obsidian | Level 7

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

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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