topic Re: Where Statement to another environment in SAS Programming

Where Statement to another environment

steve_citi — Sat, 05 May 2012 02:25:58 GMT

The following Syntax ALWAYS worked in SAS9.1, but immediately stopped working the day SAS 9.2 was installed.I do not believe that SAS9.2 lost the functionality. Instead, I believe that there may have been a change in some parameters setting when it was installed.....would anybody know?

data sastable1

set teradata.table1;

where datepart(report_date) le &cutoffdate.;

run;

The format of &cutoffdate is a pure sasdate, such as 19103.

However, after the cutover to SAS9.2 I could only get this to work if I compared report_date to something with a format of '21Apr2012'd by specifying "%sysfunc(putn(&cutoffdate.,date9.))"d.

One explanation i received was that the "Where" statement is pushed to Teradata, and Teradata does not realize that 19103 is a date.

Fair enough - but WHY did it always work in SAS9.1 ??? What could have changed??

Re: Where Statement to another environment

TomKari — Sat, 05 May 2012 03:40:30 GMT

Just.This week, I noticed a change in adifferent Access engine. I think there may be some changes from 9.1 to 9.2.

Re: Where Statement to another environment

PGStats — Sat, 05 May 2012 20:30:50 GMT

Seems to me that SAS is moving more processing to database servers with every new version. If your DBMS doesn't use the same date representation as SAS, it will not interpret correctly a simple integer representing a SAS date. Nor can SAS guess that you mean that integer to be a date and convert it into a Teradata date. The macro solution you found is the right one, short of going upstream and getting the date form which that number is derived.

Re: Where Statement to another environment

Patrick — Sun, 06 May 2012 10:57:08 GMT

As Pierre points out the likely reason for what you observe is that something gets now pushed to the database which before has been done within SAS.

I believed Datepart() is one of these SAS functions which beginning with SAS9.2 gets now pushed to the database. Interestingly its not on the list for Teradata: http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001630853.htm.

By generating a string of '21Apr2012'd you give SAS the necessary information to know that this is a date (and not a number) and so SAS will be able to pass this date in an appropriate way to the DBMS.

To see in the log what actually gets passed to the database and what's processed within SAS use the following option:

OPTIONS SASTRACE=',,,d' SASTRACELOC=SASLOG NOSTSUFFIX;